Searching for a value in Excel, returning yes or no

I have one workbook, with two separate worksheets. I want to know if the values that appear in worksheet B also appear in worksheet A, if so, I want to return a "YES". If not, I want to return a "NO".

(Example: Worksheet A is a list of overdue books. Worksheet B is the entire library).

In worksheet A, I have the following data set:

 A
1 AB123CD
2 EF456GH
3 IJ789KL
4 MN1011OP 

In worksheet B, I have the following data set:

 A Overdue
1 AB123CD ?
2 QR1516ST ?
3 EF456GH ?
4 GT0405RK ?
5 IJ789KL ?
6 MN1011OP ?

How would I structure the function in order to properly look up if the values exist in Table A?

I've been playing around with a combination of if(), vlookup(), and match(), but nothing seems to work for multiple worksheets.

3 Answers

You could use the following function

=IFERROR(IF(MATCH(A1,Sheet1!$A:$A,0),"yes",),"no")

Starting from the inside out

Match, looks in sheet1 column A to see if there is a value which matches cell A1 of the current sheet (sheet2). If there is an exact match it returns the row number.

The if statement. If match returns something (number 1 or greater) this is taken as true and returns "yes"

iferror. If match doesn't find anything it returns a na error. Iferror makes this return the last "no"

VLOOKUP should work...

=IF(ISNA(VLOOKUP(A1,Sheet1!$A:$A,1,false)),"NO","YES")

If no match is found, VLOOKUP return NA. So we see if its result ISNA? Then return NO otherwise YES

You can also use a COUNTIF statement combined with an IF:

=IF(COUNTIF(WorksheetA!$A:$A,WorksheetB!$A1)>0,"Yes","No")

This counts the number of times the contents of cell A1 are found in the A column of your first worksheet. If the number is more than 0 then the item is in the list and therefore we return a "Yes", otherwise, if the COUNTIF returns a 0 then the item was not found and we return a "No."

I use COUNTIF daily in order to identify items in one list and another (as well as duplicates).