Simple formula to check if a value appears in a different worksheet column

[Excel_apprentice]

In this scenario there are two lists of customers, some of the customers will exist on both. I want to use a simple formula to identify those customers and return TRUE if they do, FALSE if they don't. Currently, I would do this by using a combination of NOT + ISERROR + XMATCH, but I want to be able to show this to some of my colleagues and was wondering if there was a shorter formula that would do the same thing. The formula I use can be seen in the rows highlighted in yellow.

[TMS]

Try:

Please Login or Register  to view this content.

[TMS]

Or:

Please Login or Register  to view this content.

[Excel_apprentice]

Thank you so much. I love this, especially that it works with text as well as numbers. I also tried it with alphanumeric and it worked as well. Also saw that it works by changing it to =COUNTIF(Sheet2!B:B,B2)=1, which would probably be easier to explain that =COUNTIF(Sheet2!B:B,B2)>0.

Can you tell me what the >0 is doing here? Does it mean that 0 represents no matches and 1 represents a match?

[TMS]

You're welcome. Thanks for the rep.

If you just use =COUNTIF(Sheet2!B:B,B2), you get a count of matching records. So, if there is a single match, you get 1. If there is no match, you get 0. =COUNTIF(Sheet2!B:B,B2)>0 will return TRUE or FALSE, rather than 1 or 0.

If there is MORE than 1 match in that list, =COUNTIF(Sheet2!B:B,B2)>0 will still return TRUE. However, =COUNTIF(Sheet2!B:B,B2)=1 will return FALSE. Up to you . I am biased, but I would stick with =COUNTIF(Sheet2!B:B,B2)>0 . . . just in case there is any inadvertent duplication.

Does it mean that 0 represents no matches and 1 represents a match?

So, not quite. 0 represents no match. Greater than 0 ( >0 ), represents one or more matches.

[Excel_apprentice]

That makes sense. Thank you.

[TMS]

You're welcome.

And, another option is

Please Login or Register  to view this content.

So, here the COUNTIF()=0 means there is no match. Conversely, NOT(COUNTIF()=0) means there IS a match.

You have different ways to explain it. Hopefully, one will stick.