lookup with multiple condition, but one condition to satisfy is en

[Eddy Stan]

Hi

I am looking to compare 2 sheets with 20 columns
but i need to compare sheet 1 having 75 rows & sheet 2 having 2000 rows
sheet1 column d d2:d75 to compare in sheet2 column b b2:b2000
sheet1 column e e2:e75 to compare in sheet2 column b c2:c2000
sheet1 column f f2:f75 to compare in sheet2 column k k2:k2000
even if one cell in sheet1 (d,e,f) column is matching with sheet2 b,c,k
it is enough. row 1 is title in both the sheets, title is common.
i need that data to be displayed in the formula cell.
formula to be place in sheet1 b2 column and copy formula down to b75

advance thanks for your help.

 

[Max]

Presuming you want to return Sheet1's col A for the multi-criteria match
in Sheet2's col B (that's the "data to be displayed" part)

In Sheet2,

Put in B2's formula bar, then array-enter the formula
by pressing CTRL+SHIFT+ENTER:
=INDEX(Sheet2!A$2:A$2000,MATCH(1,(Sheet2!B$2:B$2000=D2)*(Sheet2!C$2:C$2000=E2)*(Sheet2!K$2:K$2000=F2),0))
Copy B2 down to B75

 

[Eddy Stan]

Presuming you want to return Sheet1's col A for the multi-criteria match
in Sheet2's col B (that's the "data to be displayed" part)

In Sheet2,

Put in B2's formula bar, then array-enter the formula
by pressing CTRL+SHIFT+ENTER:
=INDEX(Sheet2!A$2:A$2000,MATCH(1,(Sheet2!B$2:B$2000=D2)*(Sheet2!C$2:C$2000=E2)*(Sheet2!K$2:K$2000=F2),0))
Copy B2 down to B75

Hi
Thanks but is not working. it is giving error and I think it works to find
all conditions true.

Anyway i finished mannually thanks for the help.
it is for my MIS and monthly requirement so it is ok

now i am making a new request, please help me.