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

• This seems like the simplest and most elegant solution. Why are the other answers preferred by the majority? Perhaps they're more extensible? Or generally considered as best practice? Honest questions because I'd like to understand. :) Aug 17, 2022 at 12:53