MS Excel-find row # containing text

Factual Questions
1

I have an Excel spreadsheet page that contains a text string in a certain row. I know the column, but the row can change.
I need a non-VBA method of searching through the page and finding the row # of the row containing that string.
Col A
1
2
3
4
ODC
6
7

what is the command that will scan through col A and locate the row number (5) of the row containing ODC?

this can’t use VBA. It needs to run on PCs, Mac, and with OpenOffice.
Thanks

2

Will this text only appear in one row?

If so, here’s an easy-peasy kludge:

Make a column that duplicates the row numbers. (I’d just make row 1 “1”, and then each one after [(Cell above)+1] - that way you can paste/page-down until you get to the bottom of your sheet.)

If your row number column is column S, then =SUMIF(A:A,“ODC”,S:S) will give you the row number that your string appears in.

Of course, totally useless if your string appears more than once.

3

if the string you are searching for is always the same:

in the next column over (assuming your numbers are in column A and column B is blank for scratch space)

=IF(A1=“ODC”,ROW(B1),0)

=IF(A2=“ODC”,ROW(B2),0)

=IF(A3=“ODC”,ROW(B3),0)

and then in empty cells like C1 and C2, you can use:

=max(B:B)
=min(B:B)

if there is more than one instance of your string, the min/max should be different and you would know to investigate further.

4

Another method if your string is unique.
Finds first instance if it’s duplicated
Put the search string into cell A1

Row Col A Col B
1 ocd =VLOOKUP(A1,$A$2:$B$6,2,FALSE)
2 qwe =ROW()
3 asd =ROW()
4 zxc =ROW()
5 ocd =ROW()
6 ert =ROW()

5

Thanks all!

I appreciate this. I still struggle to understand vlookup, but this fragment shows how I can get the job done.

6

I’d just put my values in the A column and make the B column numbered:

asg 1
fga 2
vae 3
ODC 4
gag 5
cvr 6
vra 7

Then I’d put =VLOOKUP(“ODC”, A1:B22, 2, FALSE). It looks for ODC and returns the second column over. So it returns “4” in this case.

7

Think of VLOOKUP as searching a phone book. You have a name, but you want the phone number. So do you scan the phone numbers? No, you search the names. Then, when you find the name, you slide over a certain number of columns to the phone number.

If you were a VLOOKUP formula, you’d be =VLOOKUP(“Jones, Martha”, PhoneBook, PhoneNumber, FALSE). This says “Look for Martha Jones in the Phone Book and read me the phone number you find there.” The TRUE and FALSE on the end just tell it whether you want an exact match or a close enough match.

Does that help?

8

I know this is an old thread, but after looking here for a solution, I realized there’s an easier solution to all this… posting in case someone else is looking for the same thing:

Using the MATCH function makes this much easier. If you have, say:

A3 1
A4 2
A5 3
A6 ODC
A7 5
etc…

you can enter this formula into any cell:

=MATCH(“ODC”,A3:An,0)

where n is the last row in the array. You will get the relative number of the row in which the string appears, in this case 4. If you wanted the absolute row number (i.e. 6 for A6), you could either start with A1 in the match formula, or add 2 to the result.

Cheers

9

Absolutely. I am amazed that nobody offered that back in 2010. By far the superior solution. Therefore worth reviving a zombie.