Home > Blockchain >  XLOOKUP match emails
XLOOKUP match emails

Time:11-09

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

enter image description here

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

enter image description here

CodePudding user response:

A different approach using XLOOKUP:

=XLOOKUP(A3,$E$2:$E$5,IF(ISTEXT($E$2:$E$5),"Found"),"Not Found")

enter image description here

  • Related