lookup and return column number


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...
 
Ad

Advertisements

J

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.
 
Ad

Advertisements

C

Claus Busch

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.

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top