IF/ISBLANK formula

  • Thread starter Thread starter Missile
  • Start date Start date
M

Missile

When using this formula =IF(ISBLANK(B2,"",VLOOKUP(B2,D2:E20,2,1)) I am
getting the #N/A error or incorrect data if the value in cell B2 starts with
a 0 eg 07934.
Any suggestions as to fix this?

Thanks
 
Have you taken into account this remark for VLOOKUP function:

When searching number or date values, ensure that the data in the first
column of table_array is not stored as text values. In this case, VLOOKUP
might return an incorrect or unexpected value.
?
 
Try an exact match, TRIM the lookup value in B2, and fix ($) the ref table:
=IF(TRIM(B2)="","",VLOOKUP(TRIM(B2),$D$2:$E$20,2,0))
The above will increase the robustness of your expression, and readies it
for propagation down the column. Any joy? hit YES to celebrate it
 
... value in cell B2 starts with a 0 eg 07934.
Try adding a zero to coerce the text number in B2 to a real number for
consistent matching, viz: =VLOOKUP(B2+0, ...
 
Back
Top