Lookup formula
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) -- If this helps, please remember to click yes. "Michael" wrote: 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? |
Lookup formula
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)) -- Regards Roger Govier Paul C wrote: 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) |
All times are GMT +1. The time now is 01:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com