Need help with VLOOKUP

[Flamikey]

This is an easy one but I am having a hard time getting the synta
right. I have three sheets. In column A of each sheet are SSNs.
want to do a Vlookup of Sheet3 Col A on Col A of Sheet 1 and Sheet 2.
If I encounter an error on the VLOOKUP of Sheet 1 I want text messag
"A" returned, if I encounter an error on the VLOOKUP of sheet 2 I wan
text message "B" returned, If I find an error on both Vlookups I wan
to concatanate message "A" and message "B", if I find no errors o
either Vlookup I want no Message or BLANK message. I tried thi
nesting Or with IF with ISERROR and VLOOKUP. I think a MATCH and INDE
method might work better, but I am sure one of you can do in 30 second
what would take me an hour. Thanks

 

[Frank Kabel]

Hi

hope I understood you correctly. On sheet 3 yopu have a lookup criteria
(lets say in cell A1). You now want to test if this criteria can be
found on either sheet 1 and/or sheet 2. You don't want to return a
value from either sheet 1 or 2. For this I would use MATCH. As an
example enter the following in cell B1 on sheet 3:
=IF(ISNA(MATCH(A1,Sheet1!$A:$A,0)),IF(ISNA(MATCH(A1,Sheet2!$A:$A,0)),"M
essage A" & " " & "Message B","Message
A"),IF(ISNA(MATCH(A1,Sheet2!$A:$A,0)),"Message B",""))

HTH
Frank

 

[Flamikey]

Thanks Frank,
I will try that. I got it to work using VLOOKUP but is is a very ugly
and inefficient formula:

=IF(ISERROR(AND(VLOOKUP(A1,Sheet1!A:B,2,FALSE),VLOOKUP(A1,Sheet2!A:B,2,FALSE))),"Message
A + Message B",IF(ISERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE)),"Message
A",IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"Message B","No
Message")))

 

[Flamikey]

Actually,
I take that back. My formula didnt work. Where did my logic go wrong?
Thx.

 

[Frank Kabel]

You have to exchange the order of AND and ISERROR. Correction:

=IF(AND(ISERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE)),ISERROR(VLOOKUP(A1,Shee
t2!A:B,2,FALSE))),"Message
A + Message B",IF(ISERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE)),"Message
A",IF(ISERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE)),"Message B","No
Message")))

Frank

 

[Flamikey]

Thanks Frank. Your Match formula worked perfectly.