If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IF/ISBLANK formula
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 |
#2
|
|||
|
|||
IF/ISBLANK formula
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. ? "Missile" wrote in message ... 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 |
#3
|
|||
|
|||
IF/ISBLANK formula
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 -- Max Singapore --- "Missile" wrote: 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 |
#4
|
|||
|
|||
IF/ISBLANK formula
... 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, ... -- Max Singapore --- |
Thread Tools | |
Display Modes | |
|
|