Excel return position of the first partial string match in a table, then return position of the last partial string match in the table

Question

I have large, ever-growing table that looks like this:

I need to find the position of the first instance of "-033" and the position of the last instance of "-033". I have a cell reference for "-033" but other than that, I cannot have any cell references because I don't know the order that the data will come into the table.

I've tried a lot of different things but I keep getting hung up with methods that can't handle multiple instances of the target string.

I do not want to use VBA as I have no experience with it and this will be a shared file.

I'll be using the location data to tell me the corresponding date at the top of the column (not shown). — Justin White
– Justin White, Commented Nov 14, 2019 at 17:28
Is the image of the table showing up? It doesn't seem to be working for me — Justin White
– Justin White, Commented Nov 14, 2019 at 17:28

Scott Craner · Accepted Answer · 2019-11-14 17:32:40Z

2

For first column number:

=AGGREGATE(15,7,COLUMN($A$2:$J$100)/(ISNUMBER(SEARCH("-033",$A$2:$J$100))),1)

For last

=AGGREGATE(14,7,COLUMN($A$2:$J$100)/(ISNUMBER(SEARCH("-033",$A$2:$J$100))),1)

answered Nov 14, 2019 at 17:32

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Justin White Over a year ago

I've hit another road block with this - If there is a cell that contains "-0333", that cell is the one that gets returned, not the cell that contains "-033" only. How can I narrow my results?

Collectives™ on Stack Overflow

Excel return position of the first partial string match in a table, then return position of the last partial string match in the table

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related