Excel-Problem with Vlookup

[NukedWhale]

Here's my issue

Cells in Worksheet A have vlookup formulas that look at a cell in Worksheet B and pull the cell contents in. My problem is that the cell my vlookup formula is looking at is empty, unfortunately it's pulling a value of zero, instead of pulling emptiness.

To add another layer of confusion, it only does this occasionally.

[hazard_abio]

with Excel 2003/2000 and the vlookup across Workbooks this can happen almost all of the time.

but if you have 2 sheets (tabs) the vlookup should be working.

the only thing that I can think of would be the formatting of the cells themselves, are you comparing 2 cells that are formatted the same, or are they different, do some of the cells that are not showing stuff have apostrophes (') in the cells

With Excel 2007 it seems to work a little better,

Can you show the formula that you are using? and what version of Excel you are using as well?

[NukedWhale]

The cell containing the Vlookup formula is formatted as general

Here is the vlookup formula

=VLOOKUP(TRIM($M$5),'Left out'!$C$5:$M$24,7,FALSE)

The cell that vlookup is pulling in is formatted as general

The cell that vlookup is pulling in is empty.

I am using Excel 2003.

Does this information help at all?

Thanks for replying!

[jaclaz]

If I recall correctly it is a known "feature", in all Excel releases.

Two workarounds possible:

1) If it is just a "cosmetic" problem, in options there is somewhere an option "Show Zero Values"

2) If you need an empty as a result of the VLOOKUP, use this:

=IF(VLOOKUP(TRIM($M$5),'Left out'!$C$5:$M$24,7,FALSE)=0,"",VLOOKUP(TRIM($M$5),'Left out'!$C$5:$M$24,7,FALSE))

jaclaz

[NukedWhale]

I assume that when you say "feature" you mean bug. I figured I could fix it with an if statement, it just adds time that I shouldn't have to spend.

*Sigh*

Thanks for responding.