0

I have one workbook with 3 separate worksheets

Sheet 1 contains 2 columns.
Column A has a range of unique alphanumeric identifiers. Column B has a range of non-unique numbers.

A               B
AU0000ANZHS8    515
AU0000ATBHK3    20
AU0000KFWHZ5    10
AU0000WBCHN7    15
AU0000WBCHO5    10
AU0000WBCHQ0    10
AU000ANZHAL8    515
AU000ANZHAN4    515 
AU000WBCHAS1    25

Sheet 2 contains 1 column. Column A contains a range of 3 non-unique numbers. Some but not all of these are in Sheet1, Column B.

A
5
10
25
50
100

Sheet 3 contains 2 columns Column A has a range of unique alphanumeric identifiers. Some of these are the same as those in Sheet 1, Column A, but not necessarily in the same order.

A               
AU0000KFWHZ5        
AU0000WBCHN7        
AU0000ATBHK3    
AU0000WBCHQ0        
AU000ANZHAL8        
AU000ANZHAN4    
AU0000WBCHO5    
AU000WBCHAS1        
AU0000ANZHS8

I am trying to enter a Yes/No check into Column B.

It should do the following in B1:

  1. Lookup the value in Sheet3, A1.
  2. Find the matching value in Sheet1, Column A (in this case A3)
  3. Grab the corresponding value in Sheet1, Column B (is this case B3, which is 10)
  4. If the value in B3, exists in Sheet2, Column A return "YES", or if not return "NO"
0

As long as your data really starts on row 1 (ie. you don't have column headers), this should work!:

=if(isna(vlookup(vlookup(a1,sheet1!a:b,2,false),sheet2!a:a,1,false)),"NO","YES")

This will also return "NO" if the alphanumeric identifier on Sheet3 is NOT also found on Sheet1.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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