INDEX function displays zero when the source value is blank

[Mehmet]

Hi

Using the index and match functions, I retrieve values from another workbook (it is an array formula). Although the function I write does refer to the correct cell, the formula displays a zero, whereas the cell is actually blank (when I go to the original cell, nothing appears in the formula bar, and visually it is blank). I did put a if(isblank(...)) at the beginning and this resolves the problem (the isblank() function returns true), but it adds two more evaluations for each cell, and I am using this on a 48000+ row dataset, so this seems to lengthen the process. Besides I want to know why the index function puts a zero where it has to put a blank. I would appreciate any help

regards
Mehmet

 

[JE McGimpsey]

it puts a zero in the cell because VLOOKUP attempts to evaluate the
returned value as a number (and XL evaluates a null string as zero).

If zero is not a valid returned value (i.e., no zero's in the lookup
table), my suggestion is simply to hide them. Choose
Format/Cells/Number/Custom and enter

General;General;;@

in the textbox (or choose a different number format than General). XL's
format code have 4 sections, and by default, the first is for postive
numbers, the second for negative numbers, the third for zero and the
fourth for text.

If zero is a valid value, then I'm afraid you're stuck doing the double
evaluation.

 

[mehmet]

Thank You!