lookup and return column number

  • Thread starter Thread starter John
  • Start date Start date
J

John

I need to lookup a value in the rows then from that row, lookup another value and return the column number where it's found. Tricky part is that valuebeing searched in the columns can have multiple instances. I only want thefirst (lower column number) where it's found.

Example: Find the first column number where "apple" is "liked"

A
1 apple
2 banana

b
1 disliked
2 disliked

c
1 liked
2 disliked

d
1 liked
2 disliked

So, lookup "apple" in the rows (A1:A2). From that row number (1), find which column (B:D) contains the first instance of "liked" (C or column 2).

I tried using a MATCH to find "apple" in the rows. This gives me the row number. Easy. Now, how can I say...from that row number, find the column number containing "liked"? I'm also trying to use the most efficient formulas (i.e., sumproduct vs index/match vs super array).

Thanks in advance...
 
Should clarify that the correct answer when finding the first column number with "apple", "liked" is 2. I'm sure I need to use COLUMN or something like that too.
 
Hi John,

Am Sat, 8 Mar 2014 17:59:07 -0800 (PST) schrieb John:
Should clarify that the correct answer when finding the first column number with "apple", "liked" is 2. I'm sure I need to use COLUMN or something like that too.
Click to expand...

the address is:
=ADDRESS(MATCH("apple",A1:A2,0),MATCH("liked",A1:D1,0),4)

The column:
=COLUMN(INDIRECT(ADDRESS(MATCH("apple",A1:A2,0),MATCH("liked",A1:D1,0),4)))


Regards
Claus B.
 
Back
Top