more efficent solution to 3-pass Vlookup

  • Thread starter Thread starter Paul Simon
  • Start date Start date
P

Paul Simon

For each record within an Excel database, column D will either be the
value of column C, or, if column C is blank, the value of a Vlookup
based on column B. However, if that Vlookup would generate either a
#NA or a 0, column D is to be blank.

This formula in column D (cell D2 in this example) works, but makes 3
passes of the Vlookup.


=if(C2<>"",C2,if(or(isna(vlookup($B2,Sheet2!$A$2:$B$20,2,false)),vlookup($B2,Sheet2!$A$2:$B$20,2,false)=0),"",vlookup($B2,Sheet2!$A$2:$B$20,2,false)))


Is there a more efficient solution?

Many thanks,
Paul
 
One more efficient way is to use another column (E) which contains the
lookup
=if(C2<>"",C2,vlookup($B2,Sheet2!$A$2:$B$20,2,false))

then D2 contains
=IF(or(isna(E2),E2=0),"",E2)

hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
 
Charles, Harlan and Peo,

Thank you all very much for your excellent, and certainly far more
efficient, solutions. I appreciate your time and help very much.

Peo, the custom format idea is terrific - don't know why I didn't
think of it myself.

Many thanks to all,
Paul
 
Back
Top