more efficent solution to 3-pass Vlookup

[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

 

[Charles Williams]

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

 

[Harlan Grove]

...
...

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

...

For raw efficiency, would

=IF(ISNA(E2)+(E2=0),"",E2)

be faster?

 

[Charles Williams]

I have not tested it, but almost certainly yes<g>

Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com

 

[Paul Simon]

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