# 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.

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