XLOOKUP match emails-CodePudding

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")