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.

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$200 0=D2)*(Sheet2!C$2:C$2000=E2)*(Sheet2!K$2:K$2000=F2 ),0))
Copy B2 down to B75
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eddy Stan" wrote:
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" wrote:

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$200 0=D2)*(Sheet2!C$2:C$2000=E2)*(Sheet2!K$2:K$2000=F2 ),0))
Copy B2 down to B75
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eddy Stan" wrote:
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.


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.