Values returned by VLOOKUP

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

If the cell value identified in the column specified by col_index_num (the 3rd parameter in the function call) is a blank text cell, why is value returned by the VLOOKUP function = zero, rather than blank text? (In other cases, where the identified value is a non-blank text, the value returned by VLOOKUP is identical to that non-blank text. In all cases, the cell format in the cell where VLOOKUP places its returned value is also text.)
 
Hi Jim
try the following formula
=IF(VLOOKUP(....)="","",VLOOKUP(..))

--
Regards
Frank Kabel
Frankfurt, Germany

Jim said:
If the cell value identified in the column specified by col_index_num
Click to expand...
(the 3rd parameter in the function call) is a blank text cell, why is
value returned by the VLOOKUP function = zero, rather than blank text?
(In other cases, where the identified value is a non-blank text, the
value returned by VLOOKUP is identical to that non-blank text. In all
cases, the cell format in the cell where VLOOKUP places its returned
value is also text.)
 
As an alternative, if 0 is not a valid value for VLOOKUP() to return:

Format/Cells/Number/Custom General;-General;;@

will hide the 0 and won't require two VLOOKUPs per cell.
 
Both responses address managing the appearance and/or structure of the zero-for-blank value returned by VLOOKUP. However, I'm still unclear as to whether this phenomenon occurs only for the instance of picking up a blank and returning the number zero, or does this behavior occur for other input values that I also need to be on the lookout for?
 
Jim said:
Both responses address managing the appearance and/or structure of the
zero-for-blank value returned by VLOOKUP. However, I'm still unclear as to
whether this phenomenon occurs only for the instance of picking up a blank
and returning the number zero, or does this behavior occur for other input
values that I also need to be on the lookout for?
Click to expand...

All worksheet functions return a value to their calling cells. How XL
interprets it depends on the context - in the context of a number (the
default),an empty reference is interpreted as 0. In the context of a
string, an empty reference will be interpreted as a null string.

For instance,

=VLOOKUP(D1,A:B,2,FALSE)

will display as zero if the value in B corresponding to the lookup value
is empty.

However,

=VLOOKUP(D1,A:B,2,FALSE) & ""

since the entire formula returns a string, will display as a null
string, or blank.
 
Back
Top