Show a "Yes" or "No" result using xlookup (or similar).

[Kdog64]

This has probably been asked before, apologies as am newbie to this site.

Basically I use xlookup to check if serial numbers (alphanumeric) in one spreadsheet exist in another. Instead of returning whatever I set as the return array, is there a way to just return a Yes or No result. If this is easier achieved with another function then that is ideal too.

 

[Fluff]

Hi & welcome to MrExcel.
You could use something like

=if(isnumber(xmatch( value,range)),"Yes","No")

 

[Kdog64]

Hi & welcome to MrExcel.
You could use something like

=if(isnumber(xmatch( value,range)),"Yes","No")

Fantastic! I never knew about the xmatch function. I assume it must return a 1 if the item is matched.

 

[Fluff]

If th number is matched, it will return the position of that number in the range.

 

[Scott Huish]

You can use the regular MATCH function too:
=IF(ISNUMBER(MATCH(A1,Sheet2!A:A,0)),"YES","NO")

although I would probably use COUNTIF:
=IF(COUNTIF(Sheet2!A:A,A1),"Yes","No")

 

[Kdog64]

Thanks everyone for you help. All very interesting. Excel is so versatile and you can achieve similar results in many different ways.

I did like the countif method, I had not thought of that, but always like to read about new functions

 

[Fluff]

Glad we could help & thanks for the feedback.