Excel: search for string in row and find column

Question

I would like to search a row for a string and get the column number if there is such a string.

Is this possible by using worksheet functions in Excel?

score 12 · Accepted Answer · answered Jul 01 '13 at 15:31

You can use MATCH to give the position, e.g. you can search in row 2 for "xyz" like this

=MATCH("xyz",2:2,0)

If "xyz" is found first in J2 you get 10

if you want partial matches then you can use wildcards like

=MATCH("*xyz*",2:2,0)

so if F2 contains [abc xyz 344] you get a match with that and formula returns 6

score 2 · Answer 2 · answered Jul 01 '13 at 15:32

2

I believe VLOOKUP is the best for you. There's also others like HLOOKUP, LOOKUP and SEARCH

answered Jul 01 '13 at 15:32

Daniel Möller

score 0 · Answer 3 · answered Jul 01 '13 at 15:34

0

If the lookup value is "value" and it is in row 1 =MATCH("value",1:1,0)

answered Jul 01 '13 at 15:34

paulski

score -1 · Answer 4 · answered Jul 01 '13 at 15:42

Assuming you have something like this:

A1      A2      A3      A4 
111 aaa sss bbb,

you could write something of this sort:

=FIND("$", CELL("address",OFFSET($A$2, 0,MATCH(111,A2:D2,1)-COLUMN($A$2)+1)), 2)

Ugly, but works (assuming that's what you were looking for)...

4 Answers4