Index match with multiple criteria(different than usual)


T

Thiago Correia

Exemple:

Worksheet1
A B C D
Result Value1 Value2 Value3
1 4 5 6
2 7 8
3 9


Worksheet2
A B
Search Result
6 1
9 3
7 2

The result in Worksheet2!B should be the value of worksheet1!A if worksheet1!B or worksheet1!C or worksheet1!D contains the same value of Worksheet2!A


I tried to use VLOOKUP AND INDEX MATCH functions, but I couldn't find any answer.

If anybody could help me on this formula I would appreciate it.

Thanks in advance,
Thiago
 
Ad

Advertisements

C

Claus Busch

Hi,

Am Thu, 28 Mar 2013 13:02:22 -0700 (PDT) schrieb Thiago Correia:
Worksheet1
A B C D
Result Value1 Value2 Value3
1 4 5 6
2 7 8
3 9


Worksheet2
A B
Search Result
6 1
9 3
7 2

in worksheet2 B2 try:
=INDEX(Sheet1!$A$1:$A$100,MIN(IF(Sheet1!$B$2:$D$100=A2,ROW($2:$100))))
and enter the array formula with CTRL+Shift+Enter


Regards
Claus Busch
 
T

Thiago Correia

Didn't work here, maybe I'm doing something wrong... Can anybody check Claus' sugestion?
 
Ad

Advertisements


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