Lookup formula

[Michael]

Hi, I have a lookup that is looking at 2 different worksheet and the formula
is

=IF(ISERROR(IF($D1="ruth baybutt",HLOOKUP(A$3,ruth
baybut!F:N,3,0),HLOOKUP(A$3,sheet2!F:N,3,0))),0,IF($D1="ruth
baybutt",HLOOKUP(A$3,ruth baybut!F:N,3,0),HLOOKUP(A$3,sheet2!F:N,3,0)))

So the formula is asking if cell D1 = Ruth Baybut then lookup the value on
the ruth baybut sheet otherwise look on sheet2.

Is there a smaller and quicker formula for this please?

 

[Paul C]

If you are using Excel 2007 you can use the new IFERROR function. (you dont
have to type the conditions twice)

=IFERROR(IF($D1="ruth baybutt",HLOOKUP(A$3,ruth
baybut!F:N,3,0),HLOOKUP(A$3,sheet2!F:N,3,0)),0)

If you are using 2003 the formula is about as compact as can be.

For both versions, the formula would calculate more quickly if you did not
use whole column references (F:N) and instead limited the lookup to the data
area (i.e. $F$1:$N$400)

 

[Roger Govier]

Hi

For XL 2003, you could have a more efficient formula by using

=IF(AND($D1="ruth baybutt",Countif(ruth baybut!F:F,A$3)),
HLOOKUP(A$3,ruth baybut!F:N,3,0),HLOOKUP(A$3,sheet2!F:N,3,0))