How to get a blank cell instead of a "0"

[ABlevins]

I used (various and many) spreadsheets at www.contextures.com to create a
2-tier dependent drop-list for addressing invoices. The third, fourth, and
fifth lines use IF functions to call data from a named range on another sheet
in the workbook. However, some of the addresses do not have a fifth line.
Short of going through all of the entries (over 150), how can I alter the
formula (below) to yield a blank cell instead of a zero.

=IF(ISERROR(VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0)),"",VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0))]

 

[T. Valko]

You'd have to add another IF(VLOOKUP(.....)=0,"",VLOOKUP(.....)) which will
make the formula pretty long.

Or, use a helper cell with just this:

=VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0)

Then use a formula like this that pulls the result from the helper cell:

=IF(ISERROR(helper_cell),"",IF(helper_cell=0,"",helper_cell))

 

[Dave Peterson]

Just a suggestion...

If a 0 is really being returned, the OP may want to see that. But if the
"sending" cell was empty, the OP may want to hide that 0.

I'd use something like:

=if(iserror(vlookup(...)),"",if(vlookup(...)="","",vlookup(...))))

You'd have to add another IF(VLOOKUP(.....)=0,"",VLOOKUP(.....)) which will
make the formula pretty long.

Or, use a helper cell with just this:

=VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0)

Then use a formula like this that pulls the result from the helper cell:

=IF(ISERROR(helper_cell),"",IF(helper_cell=0,"",helper_cell))

--
Biff
Microsoft Excel MVP

I used (various and many) spreadsheets at www.contextures.com to create a
2-tier dependent drop-list for addressing invoices. The third, fourth,
and
fifth lines use IF functions to call data from a named range on another
sheet
in the workbook. However, some of the addresses do not have a fifth line.
Short of going through all of the entries (over 150), how can I alter the
formula (below) to yield a blank cell instead of a zero.

=IF(ISERROR(VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0)),"",VLOOKUP($D$11,INDIRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0))]