Using IF, VLOOKUP, and ISNA to find Commonalities

[ExcelBeginner1203]

I have 3 sheets of data. Using IF, VLOOKUP, ISNA/MATCH, I have to create a formula in sheet 3 to find out if the value(A2), exists in either sheet 1, or sheet 2 or both. If the value is in sheet 1 exclusively I need the formula to generate 1, if the value is in sheet 2 exclusively I need the formula to generate a 2, if its in both I need it to generate a 3. Any help would be appreciated.

[Speshul]

Guessing on the ranges....here is a formula structure that will do what you need.

=SUM(IF(ISERROR(VLOOKUP(A2,Sheet1!A1:X500,3,0)),0,1),IF(ISERROR(VLOOKUP(A2,Sheet2!A1:X500,3,0)),0,2))

basically,
if the vlookup on sheet 1 is an error (no match), return a zero. if there is a match, return a 1
and
if the vlookup on sheet 2 is an error, (no match) return a zero. if there is a match, return a 2

Sum those numbers together.

if there is a match on sheet 1 but not sheet 2 there will be a =SUM(1,0) = 1
Match on 2 but not on 1 =SUM(0,2) = 2
Match on both =SUM(1,2) = 3

Hope this helps/makes sense!


Welcome to ExcelForum.

[ExcelBeginner1203]

Thank you so much!