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!

=INDEX(Sheet2!E1:E100,MATCH(1,(Sheet2!A1:A100=A1)* (Sheet12!B1:B100=A2)*(Shee
t2!C1:C100=A3)*(Sheet2!D1:D100=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)

"deeds" wrote in message
...
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!

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"?

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



"deeds" wrote:

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!

Thanks to both of you! I figured out a solution. Thanks for the ideas!

"JMB" wrote:

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"?

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



"deeds" wrote:

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!