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,"",he lper_cell))
--
Biff
Microsoft Excel MVP
"ABlevins" wrote in message
...
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,C HDLookup,2,0)&"Lookup"),4,0)),"",VLOOKUP($D$11,IND IRECT(VLOOKUP($D$10,CHDLookup,2,0)&"Lookup"),4,0))]