12

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.

Improve this question

3 Answers 3

Reset to default
21

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"

Improve this answer
0
6

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

Improve this answer
2

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

Improve this answer
1
  • 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. :)
    – bpcookson
    Commented Aug 17, 2022 at 12:53

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .