How do I make VLOOKUP result show blank if range is empty?

D

dafan

Well-known Member

Joined

May 6, 2008

Messages

692

• Jun 24, 2008

• #2

Code:

=IF(A8="","",IF(VLOOKUP($A$8,$A$10:$I$702,2,FALSE)=0,"",VLOOKUP($A$8,$A$10:$I$702,2,FALSE))

Problem is VLOOKUP returns 0 if the 2nd column is empty, so you can't have any values of '0' in column 2.

 

Upvote 0

tkroper

Active Member

Joined

Feb 2, 2007

Messages

255

• Jun 24, 2008

• #3

Excellent! Thank you dafan

 

Upvote 0

HalfAce

MrExcel MVP

Joined

Apr 6, 2003

Messages

9,456

• Jun 24, 2008

• #4

tkroper,
Same thing using the OR function instead of nested IFs...

Code:

=IF(OR(A8="",VLOOKUP($A$8,$A$10:$I$702,2,0)=0),"",VLOOKUP($A$8,$A$10:$I$702,2,0))

Just another way. Hope it helps.

 

Upvote 0

tkroper

Active Member

Joined

Feb 2, 2007

Messages

255

• Jun 25, 2008

• #5

Thank you HalfAce! Works great, only it returns "N/A" if A8 is blank. Is there a way to correct that?

Todd

 

Upvote 0

A

axemaster

New Member

Joined

Jul 22, 2010

Messages

1

• Jun 7, 2012

• #6

Code:

=IF(ISNA(VLOOKUP($A$8,$A$10:$I$720,2,FALSE)),"",VLOOKUP($A$8,$A$10:$I$720,2,FALSE))

I have the same problem, and I found this as a solution.

 

Upvote 0

taurean

Well-known Member

Joined

Jun 17, 2011

Messages

2,190

Office Version

1. 365
2. 2016

Platform

1. Windows

• Jun 7, 2012

• #7

Hi axemaster,

Welcome to MrExcel.

tkroper's requirement is little different than yours. His requirement was:
If the column for the LookUp_Value returns blank then instead of showing 0 show the cell as blank.

Edit:
And if you have Excel 2007+ version then you can also use:
=IFERROR(VLOOKUP($A$8,$A$10:$I$720,2,FALSE),"")

 

Upvote 0