Multiple If/Vlookup/Match

[Guest]

I know this answer is out there somewhere...I just can't seem to get any to
work. What I want to do in Sheet1 is lookup cells A1,A2,A3,A4 in Sheet2
cells A1,A2,A3,A4. If it finds a match of all 4 criteria, then return "OK"
otherwise return "NO MATCH". I am very close with variations of Match,
Vlookup, etc....please help!

Thanks in advance!

 

[Bob Phillips]

=INDEX(Sheet2!E1:E100,MATCH(1,(Sheet2!A1:A100=A1)*(Sheet12!B1:B100=A2)*(Shee
t2!C1:C100=A3)*(Sheet2!D1100=A4),0)

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Do you mean if the data in A1:A4 is the same as the data in Sheet2!A1:A4 (but
perhaps in a different order), then return "Match"?

Mayb
=IF(SUMPRODUCT(--(ISNUMBER(MATCH(A1:A4,Sheet2!A1:A4,0))))=ROWS(A1:A4),"Match","No Match")