How to check if the values in a range, let's say B2:B10 are present in range let's say A2:-CodePudding

I can do the same manually if the range is small. I need the formula if the range is too big to imagine. The result should be in Column C letting me know the values of col B that are present in col A. Can we use look ups, if so, then how so.

CodePudding user response：

Try COUNTIF() like-

=IF(COUNTIF($A$2:$A$6,B2:B10)=0,"Not exist","Exist")

For older version of excel try-

=IF(ISNUMBER(MATCH(B2,$A$2:$A$6,0)),"exist","not exist")

CodePudding user response：

Try using XMATCH() & ISNA() will be relatively fast if using MS365

• Formula used in cell C2

=IF(ISNA(XMATCH(B2,$A$2:$A$6,0)),"Not Found","Found")

If you are not using MS365 then use MATCH() with ISNA()

• Formula used in cell D2

=IF(ISNA(MATCH(B2,$A$2:$A$6,0)),"Not Found","Found")