Index match with multiple criteria(different than usual)

[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

 

[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

 

[Thiago Correia]

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

 

[Claus Busch]

Hi Thiago,

Am Mon, 1 Apr 2013 09:30:29 -0700 (PDT) schrieb Thiago Correia:

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

have you array entered the formula?
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!191
for the workbook "Thiago"


Regards
Claus Busch