Vlookup result #N/A

  • Thread starter Thread starter Pablo
  • Start date Start date
P

Pablo

When I am using the Vlookup function (false) and the
reference item as a empty value I get a result of #N/A.
Is there a way to set the #N/A to blank?

Thanks,
 
One way

=IF(ISNUMBER(MATCH(A1,B2:B100,0)),VLOOKUP(A1,B2:C100,2,0),"")


where A1 is the lookup value and B2:C100 the table, last 0 equals FALSE
tells excel that if there is no match in the leftmost column of the table,
return blank cell,
if match do the lookup
 
For example:

=IF(ISERROR(VLOOKUP(A1,$A$1:$B$10,2,FALSE)),"",(VLOOKUP(A1,$A$1:$B$10,2,FALS
E)))

Is this what you mean?
 
Better to use

=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

since ISERROR will be TRUE not only for #N/A errors, but also
#VALUE!, #REF etc. errors.

Also note that it's slightly faster to use

=IF(ISNA(MATCH(A1,$A$10, FALSE)),"",VLOOKUP(...))
 
Back
Top