[Solved] How to return result form Vlookup with IF AND statements

[jing]

Hi,

Am trying to match the client ID from a range where the client ID is associated to one or more sub-customers. From the matched ID, we need to return the YES or NO status depending on the sub-customers.

Vlookup ID, IF ID Match, look through sub-customer column
If sub-customer = Yes and sub-customer = Yes return YES
If sub-customer = No and sub-customer = Yes return NO
If sub-customer = No and sub-customer = No return NO

But am not sure how to use the formula as there was an error "#NAME".
This was what I had used and it had an error - =VLOOKUP(A2,IF(AND(Sheet1!A:R,6="Yes",Sheet1!A:R,6="No"),"No", IF(AND(Sheet1!A:R,6=No,Sheet1!A:R,6="No"),"No", IF(AND(Sheet1!A:R,6="Yes",Sheet1!A:R,6="Yes"),"Yes"))),"")

Do share on how it should be done. Thank you.

 

[Afrina Nafisa]

Hello,
Welcome to the ExcelDemy forum. According to your post, you wish to get the correct formula to match the client ID from a range where the client ID is associated with one or more sub-customers using VLOOKUP, IF, and AND Statements. Please follow the below formula to execute this process.
=IF(AND(VLOOKUP(A2,Sheet1!A:B,2,FALSE)="Yes",VLOOKUP(A2,Sheet1!A:B,2,FALSE)="Yes"),"Yes",IF(AND(VLOOKUP(A2,Sheet1!A:B,2,FALSE)="No",VLOOKUP(A2,Sheet1!A:B,2,FALSE)="Yes"),"No",IF(AND(VLOOKUP(A2,Sheet1!A:B,2,FALSE)="No",VLOOKUP(A2,Sheet1!A:B,2,FALSE)="No"),"No","")))