Vlookup returns "0"

C

Carolyn

I am using the following formula
=VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE
and it is returning a zero if there is no data found in
that cell. If there is no data found I would like it to
display nothing.
How can I do this?
 
E

Earl Kiosterud

Carolyn,

There are a couple ways so do this. One is to test it, which makes for
doing the VLOOKUP function twice.

1)
=if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)=0, "" ,
VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE))

This gets a little messy. Better yet, and simpler is to hide the original
cell (column, whatever), and refer to it in another column. If your
original formula is in B2, put this in another cell:

=if( B2 = 0, "", B2).

Now hide column B, and let this one display.

2) Simply format the original formula (Format - Cells - Number - Custom)
with

General; General;;General

The third term is nothing, so when it yields a 0, you get nothing.

3)

Use Tools - Options - View - deselect "zero values." This will apply to all
cells in the worksheet with 0.
 
M

Mike A

Carolyn-


Here are a couple of approaches:

1. Wrap your VLOOKUP in an IF() function:
IF(VLOOKUP(A1,E1:H1,2,FALSE)=0,"",VLOOKUP(A1,E1:H1,2,FALSE))

2. Use conditional formatting:
Format -> Conditional Formatting...
Cell value is | equal to | 0
Click on 'Format' and set the text color to white (or
whatever the fill color for the cell is.)

I use both of these methods all the time - VLOOKUP is one of my most
frequently used functions.



Mike Argy
Custom Office solutions and
Windows/UNIX applications
 
D

Dave Peterson

I'd use this to check for an empty cell.

=if(VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE)="","",
VLOOKUP($A$1,'Data Sheet'!$A$11:$BH$15,3,FALSE))

Then if a 0 were returned, you know that it wasn't an empty cell--it was really
0.
 
Top