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


G

Guest

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

Advertisements

G

Guest

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
 
Ad

Advertisements

G

Guest

Max said:
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.
 

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