vlookup to return Yes and No values

[Junior9]

Is it possible to write a vlookup that will return a value of Yes if the value is true and a value of No if the value is false?

I have data in column A on sheet 1 so the lookup value starts in cell A2 and the table array is on sheet 2 in columns A and B so the column index is 2.

 

[Aladin Akyurek]

Is it possible to write a vlookup that will return a value of Yes if the value is true and a value of No if the value is false?

I have data in column A on sheet 1 so the lookup value starts in cell A2 and the table array is on sheet 2 in columns A and B so the column index is 2.

If "the value is true" means a successful match...

=IF(ISNUMBER(MATCH(LookupValue,MatchRange,0)),"Yes","No")

MatchRange is the range against which LookupValue is matched (more often than not, the first column of your table).

 

[kwennerberg]

Here is generic sample:
=IF(ISERROR(VLOOKUP("YourLookupValue",A1:B26,1,FALSE)),"NO","YES")
Apply your own values for lookupvalue, range, and column number. Excel Help can assist in getting the syntax right.

 

[Junior9]

Thanks both of you for your help, the second one did it for me - cheers!

 

[Aladin Akyurek]

Thanks both of you for your help, the second one did it for me - cheers!

You need to do the required substitution...

=IF(ISNUMBER(MATCH(LookupValue,$A$1:$A$26,0)),"Yes","No")

 

[Prasadk2k]

Wohoo..it works like magic.

=IF(ISERROR(VLOOKUP(AJ2,'Valid rates'!F:F,1,0)),"Valid","Invalid")

I wanted to know if my rates are valid or invalid from my rate database. And it was done.

Thanks team a ton!!!

 

[Aladin Akyurek]

Wohoo..it works like magic.

=IF(ISERROR(VLOOKUP(AJ2,'Valid rates'!F:F,1,0)),"Valid","Invalid")

I wanted to know if my rates are valid or invalid from my rate database. And it was done.

Thanks team a ton!!!

=IF(ISNUMBER(MATCH(AJ2,'Valid rates'!F:F,0)),"Valid","Invalid")

would also work.

 

[Jayfore]

This thread has been very useful for me to refresh my memory on VLOOKUP usage... But unfortunately I am still unable to do what I need to do, which is pretty much what the OP asked for. Can anyone help a guy who is rusty with Excel and trying to get back to doing more useful stuff with it?

I have a spreadsheet w/ multiple worksheets (tabs). One tab ("FULL") has a list of all the servers in a particular region. The other tab ("SUBSET") has a subset of the list. On the FULL tab I have added a column where I want YES/No to indicate whether or not the particular server name exists in the SUBSET sheet.

Here is what I have now, but it's not working. Any idea what I might be doing wrong?

=IF(ISERROR(VLOOKUP($A214,EMEA_Branches!$A$2:$R$120,2,FALSE)),"NO","YES")

 

[Jayfore]

This thread has been very useful for me to refresh my memory on VLOOKUP usage... But unfortunately I am still unable to do what I need to do, which is pretty much what the OP asked for. Can anyone help a guy who is rusty with Excel and trying to get back to doing more useful stuff with it?

I have a spreadsheet w/ multiple worksheets (tabs). One tab ("FULL") has a list of all the servers in a particular region. The other tab ("SUBSET") has a subset of the list. On the FULL tab I have added a column where I want YES/No to indicate whether or not the particular server name exists in the SUBSET sheet.

Here is what I have now, but it's not working. Any idea what I might be doing wrong?

=IF(ISERROR(VLOOKUP($A214,EMEA_Branches!$A$2:$R$120,2,FALSE)),"NO","YES")

Looks like I can't edit my previous post... But I noticed an error. This is the actual formula that I am trying to use:

=IF(ISERROR(VLOOKUP($A214,SUBSET!$A$2:$R$120,2,FALSE)),"NO","YES")
...Where A214 is a cell in the FULL worksheet

Thank you!

 

[Aladin Akyurek]

Looks like I can't edit my previous post... But I noticed an error. This is the actual formula that I am trying to use:

=IF(ISERROR(VLOOKUP($A214,SUBSET!$A$2:$R$120,2,FALSE)),"NO","YES")
...Where A214 is a cell in the FULL worksheet

Thank you!

What is the problem you encounter?