vLopkup returning blank cells, but they're showing as zero 1

[McGinley57]

The problem: vLookup successfully finds a value in another sheet, but when the return value is blank, the calling cell shows a zero.

I've done everything--cleared the blanks cells, applied the text format option--that seemed obvious to me, but no luck.

Some Google searches tell me that the answer is to build a more complex function. I don't think I should have to do this in that I have other vlookup calls that behave the way I want them to (blanks do not result in zeros).

Does anyone know what's going on here? Thanks in advance.

 

[AnotherHiggins]

I don't think I should have to do this in that I have other vlookup calls that behave the way I want them to (blanks do not result in zeros)

Please provide an example.

Try this: On a brand new spreadsheet, type this into A1: [COLOR=blue white]=B1[/color]

B1 is empty, but A1 displays 0. That's just the way it works. Same with VLookups.

If you don't want to show [!]ANY[/!] zero values anywhere on the sheet, you can go to Tools > Options > View and uncheck Zero Values at the bottom.

Otherwise, you do need to create a more complex formula - but only slightly more complex.

Change your formula to look like this:
[tab][COLOR=blue white]=If(YourVLookupFormula="","",YourVLookupFormula)[/color]
(where YourVLookupFormula is - you guessed it - your VLookup formula)

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[McGinley57]

Thanks, John. I'll give your function a whirl. It's a lot cleaner than what I came up with on Google.

Also, apologies. I went back and looked for examples of blanks instead of zeros and realized there weren't any. I don't know what I was thinking.

 

[AnotherHiggins]

No problem. 'Happens to everyone now and again.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[McGinley57]

I'm enjoying one right now. Cheers.