vlookup return to blank when the cell lookup is blank

[thithan]

Good Day,

Need your help on this, I'm making a dashboard, my problem is my vlookup is not retuning to blank instead it displays 0%.

My formula is:
=IFERROR(IF($B$4="",VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0),VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)),"")


Thanks.

 

[Robby19]

Not tested, but this should work.

=IFERROR(IF(OR(VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)=0,VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)=0),"",IF($B$4="",VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0),VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0))),"")

Alternatively, you could use Conditional Formatting for the appropriate cells so that any cell = 0 would change the color to the same as your background. Example, if cell background is white and formula result is 0, then the conditional formatting could change the 0 to white automatically thus hiding it from view.

 

[thithan]

thanks for this, but it does not work, it display blanks on the entire table. Actually I've tried the conditional formatting but my lookup list contains 0% values and "blank", and upon applying conditional formatting even the 0% values also being hiding.

 

[Robby19]

What doesn't work on it? I still shows the 0% or displays an error? From original post, it seemed as though you wanted to turn all 0 values in blanks.

 

[Robby19]

If your VLOOKUP is also pulling the work BLANK, try;

=IFERROR(IF(OR(VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)=0,VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)="Blank",VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)=0,VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)="Blank"),"",IF($B$4="",VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0),VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0))),"")

I will be the first to admit that this is not a very nice looking formula, but it should get the job done. It is pulling the results from your 2 vlookup's and if the result is a 0 or Blank, the it replaces it with "" (null).

 

[Robby19]

Added the AND $B$4="" to the first argument.

=IFERROR(IF(AND(OR(VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)=0,VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)="Blank",VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)=0,VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0)="Blank"),$B$4=""),"",IF($B$4="",VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0),VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,DASHBOARD!D$1,0))),"")

 

[thithan]

It displays nothing on my table.

 

[Aladin Akyurek]

@ thithan

A couple of questions:

1) Is this formula located on the DASHBOARD sheet?

2) Does the formula return text if successful?

 

[thithan]

Hi Aladin,

1. Yes formula is located on dashboard worksheet
2. Lookup list is actually a pivot table containing only number (%) values

 

[Aladin Akyurek]

Hi Aladin,

1. Yes formula is located on dashboard worksheet
2. Lookup list is actually a pivot table containing only number (%) values

=IFERROR(IF(VLOOKUP($A$4&$C8,PIVOT!$A$5:$AC$472,D$1,0)="","",VLOOKUP($B$4&$C8,PIVOT!$A$5:$AC$472,D$1,0)),"")

This omits your $B$4="" test.