Vlookup returning a blank cell

[dhally]

Hello;
I have viewed numerous threads regarding: Vlookup returning a blank cell if is lookup returns #NA.

Yet, when I use the the accepted formula format given on many different websites I get a return of a number zero when I really want a return of a blank cell. Here is my original Vlookup formula:
=VLOOKUP(A300,'VLOOKUP_FOR_BLANK_RANTALA_Request For SAP Upload Form.xlsx'!LookupContainer,12,FALSE)

Here is the formula I had hoped would return a blank cell:
=IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2))

Any help would be very appreciated. Thank you in advance
dh

 

[MrKowz]

Welcome to the forums!

What type of data is the VLOOKUP returning? Is it returning numbers or strings?

 

[Jonmo1]

Welcome to the board...

There is a difference between.
#N/A and returning 0

#N/A means the vlookup did not find a match for the lookup value
returning 0 means it DID find a match, but the corresponding cell in adjescent column to the lookup value is blank or 0.

So IF(ISNA(...
will only capture the error when the match is not found.
But if the corresponding value is blank or 0, it will still be returned as 0.

You have to do this..

=IF(ISNA(VLOOKUP(...)),"",IF(VLOOKUP(...)=0,"",VLOOKUP(...)))


Hope that helps.

 

[dhally]

Thank you for your reply.

The value being returned is a zero.

 

[MrKowz]

Let me rephrase, what type of data does your VLOOKUP return for the values that you expect? The reason I am asking is because we can use a custom number format if the data you are returning are strings, or we might need to create a more elaborate formula if you are returning numbers.

For example, if at some poing in your data, you are EXPECTED to return 0 (when the actual value returned should be 0, not blank), then we don't want to use a custom number format.

 

[T. Valko]

Hello;
I have viewed numerous threads regarding: Vlookup returning a blank cell if is lookup returns #NA.

Yet, when I use the the accepted formula format given on many different websites I get a return of a number zero when I really want a return of a blank cell. Here is my original Vlookup formula:
=VLOOKUP(A300,'VLOOKUP_FOR_BLANK_RANTALA_Request For SAP Upload Form.xlsx'!LookupContainer,12,FALSE)

Here is the formula I had hoped would return a blank cell:
=IF(ISNA(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2)),"",(VLOOKUP(A301,'VLOOKUP CONTAINER'!K301:L301,2))

Any help would be very appreciated. Thank you in advance
dh

What version of Excel are you using?

If you get a result of 0 that means the lookup value was found but the corresponding cell in the table array is either empty or numeric 0.

If the corresponding cell is EMPTY and the data type of the normally returned value is TEXT then you can use something like this:

=T(VLOOKUP(...))

 

[dhally]

Ah...of course! Still new to formulas, so thank you for your patience. Your suggestion worked.

 

[MrKowz]

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Al</td><td style=";">Apple</td><td style="text-align: right;;"></td><td style=";">Name:</td><td style=";">Sam</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Bill</td><td style=";">Pear</td><td style="text-align: right;;"></td><td style=";">Fruit:</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Jane</td><td style=";">Orange</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Sam</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Zane</td><td style=";">Kiwi</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Al</td><td style="text-align: right;;">0%</td><td style="text-align: right;;"></td><td style=";">Name:</td><td style=";">Sam</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Bill</td><td style="text-align: right;;">3%</td><td style="text-align: right;;"></td><td style=";">Fruit:</td><td style=";"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Jane</td><td style="text-align: right;;">5%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">Sam</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Zane</td><td style="text-align: right;;">6%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E3</th><td style="text-align:left">=VLOOKUP(<font color="Blue">E2,A2:B6,2,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">E11</th><td style="text-align:left">=IF(<font color="Blue">VLOOKUP(<font color="Red">E10,A10:B14,2,0</font>)="","",VLOOKUP(<font color="Red">E10,A10:B14,2,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
The top example uses a custom number format of General;General;"" to remove the 0. The bottom example uses a different formula to handle the blank.

 

[dhally]

T. Valko, v2007

 

[T. Valko]

T. Valko, v2007

Ok, if the value being returned is normally a TEXT string...

=IFERROR(T(VLOOKUP(...)),"")