I am trying to write an excel XLOOKUP
query.
Where I have 2 table -
Table 1
Email Found/Not Found
[email protected]
[email protected]
[email protected]
[email protected]
Table 2
Email
[email protected]
[email protected]
In table 1
I want to find if any of the emails
can be found in table 2
. If the emails
is found the result should be found
else not found
Expected result
Email Found/Not Found
[email protected] Not Found
[email protected] Found
[email protected] Found
[email protected] Not Found
I have tried this query but it is giving me an error message
=XLOOKUP(A2;table2!A2:A5;"Found";"";0)
CodePudding user response:
Use Match:
=IF(ISNUMBER(MATCH(A2,table2!$A$2:$A$5,0)),"Found","Not Found")
If you really want to use XLOOKUP, we need to do some trickery.
=LET(lkp,table2!$A$2:$A$5,XLOOKUP(A2,lkp,INDEX({"found"},SEQUENCE(ROWS(lkp),,1,0)),"not found",0))
CodePudding user response:
A different approach using XLOOKUP:
=XLOOKUP(A3,$E$2:$E$5,IF(ISTEXT($E$2:$E$5),"Found"),"Not Found")