Home » Forums » Open Source Learning Center » Question and Answer

Vlookup

Submitted by tcoia on 9 January, 2013 - 14:06

Using a VLookup formula, I need to change the value that displays when my condition is not met.. its currently set to #N/A.. I need it to display "YES" adding ad If statement is creating a circular reference.. (not sure why).

thanks

‹ Changing the Background color How to Change a register of one ID with many type of models in columns and change to many rows with ID ›
»

Mask error value from VLOOKUP

Submitted by manny on 9 January, 2013 - 15:22.

Hi,

You can use the following:

 = IF(ISNA(VLOOKUP(C1, $A$1:$B$100, 2, False)), "YES", VLOOKUP(C1, $A$1:$B$100, 2, False))

 

VLOOKUP can return #VALUE! if column index is less than 1 and #REF! if column index is greater than the number of columns in table array. If you want to ingore this error too and your Excel version is 2007-2010, you can use a shorter formula:

 = IFERROR(VLOOKUP(C1, $A$1:$B$100, 2, False), "YES")

 

Best regards.

»