I have 2 data sources and i want to lookup data using Vlookup by using unique ID,
1st data source will show "X" if the ID locate there,
2nd data source will show "Y" if the ID locate there, the other responses of ID lookup will show nothing ""

Data source 1:
20000543
10056577
10032255
10008287
10011657
10008119
10008212
10007880
10052842
10007959
10062445
10011361
10008079
10008836
10007792
10052651
10007793
10047404
10007913
10007528
10008252
10007388
10007311
10057445
10036263
20004767
20004010
20001276
20010704
10039655
10062427
20010703
10048331

Data source 2:
20003695
20010673
10011463
10021645
10007439
20001123
10008276
10055885
10007718
10007587
10007364
10055257
10007651
10056216
10054797
10052500
10059984
10027674
10010153
10007808
10008182
10007763
10007428
10008102
10050130
10037579
10021909
10009539
10057979

I want to lookup value by using ID "10008102" which is locate in Data source 2 than it will show "Y" for final result,

How can I achieve this?

Much appreciate that.
Thank you

CodePudding user response：

Please don't kill me for the overkill, but I came up with this:

=IF(IFNA(MATCH(A1,Sheet2!A$1:A$29,0),"N")="N","N","Y")

The story:

• Match(...,...,0) searches for an exact match (hence the zero).
  • you need to look for a relative reference, like A1 (without dollarsigns), so that you can drag down and use another cell reference at any time
  • you need to look inside a fix array, like A$1:A$29, so that, when you drag down, you don't modify the array you're looking in.
• Match() either gives a number or an #N/A error in case not found.
• IFNA() translates #N/A to "N" (but there's no else-case in IFNA())
• IF() has an else-case.

I'm inviting Excel wizards to come up with a simpler solution :-)

CodePudding user response：

Do you possibly want to account for the value existing in both or none of the columns?

=IF(AND(COUNTIF(A:A,E2)>0,COUNTIF(B:B;E2)>0),"X&Y",
IF(COUNTIF(A:A,E2)>0,"X",
IF(COUNTIF(B:B,E2)>0,"Y",
"NONE")))