Lookup formula

M

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?
 
P

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)
 
R

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top