8

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.

18

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"

4

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

0

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.