formula to lookup a value in named range

[plost33]

I am trying to have a formula check to see if a value is listed within a named range of "IndonesiaVendors".

Formula should return "YES" if value is in the named range and NO" if it is not. should be a 100% match.

hope someone can assist.

 

[Trevor G]

Is the check just to check the range or place Yes or No in additional cells in another column for each row being checked?

Can you give some idea what it is to check for?

 

[plost33]

i am going to use this a part of a large if statement formula. the final product i need is for take the value in column b and multiply it by .1 if the value in coulmn A is within the named range "IndonesiaVendors.

Column A Column B Column C
Vendor Name Cost Formula


the named range is on a hidden sheet.

 

[stilesbn]

This seems a bit cumbersome but it should work.

=IF(IFERROR(VLOOKUP("Value",IndonesiaVendors,1,FALSE),"NO")="NO","NO","YES")

Just replace "Value" with what you are looking for.

 

[T. Valko]

I am trying to have a formula check to see if a value is listed within a named range of "IndonesiaVendors".

Formula should return "YES" if value is in the named range and NO" if it is not. should be a 100% match.

hope someone can assist.

Try this...

A1 = some value

=IF(COUNTIF(IndonesiaVendors,A1),"Yes","No")

 

[plost33]

thanks guys. both of those formulas work great.

 

[T. Valko]

thanks guys. both of those formulas work great.

You're welcome. Thanks for the feedback!

 

[Aladin Akyurek]

I am trying to have a formula check to see if a value is listed within a named range of "IndonesiaVendors".

Formula should return "YES" if value is in the named range and NO" if it is not. should be a 100% match.

hope someone can assist.

A tad faster to invoke...

=ISNUMBER(MATCH(Value,IndonesiaVendors,0))+0

where a result of 1 means a hit, 0 no-hit.

 

[T. Valko]

A tad faster to invoke...

=ISNUMBER(MATCH(Value,IndonesiaVendors,0))+0

where a result of 1 means a hit, 0 no-hit.

You should note that, as written, the named range must be a 1 dimensional array for MATCH to work.