Multiple IF VLOOKUP formala

[agriswold]

Hello -
I have two lists on two separate worksheets in a workbook. In a third worksheet in column A, I need to check if the values in a list match either of the values in the lists on other two tabs. If the value is found in the first worksheet, I want to return "1" and if the value is found in the 2nd worksheet I want to return "2". If it's not found anywhere, I need to return "3". I am trying to make a formula with multiple IF(VLOOKUP) functions work, but not having success.

Here is what I am trying:
=IF(VLOOKUP(A1,'Worksheet1'!A:A,1,FALSE),"1",IF(VLOOKUP(A1,'Worksheet2'!A:A,1,FALSE),"2","3"))
I am getting a #VALUE error for values that are in either Worksheet 1 or 2, and an #N/A error for values that are not in either worksheet.

Any suggestions?

Thanks in advance!

 

[steve the fish]

Its because the IF requires a true or false result from the logical test. The VLOOKUP will be producing whatever is in A1 so not a true or false result. Try this:

=IF(ISNUMBER(MATCH(A1,Worksheet1!A:A,0)),1,IF(ISNUMBER(MATCH(A1,Worksheet2!A:A,0)),2,3))

 

[agriswold]

Thanks very much! This worked perfectly.