Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Utilizing a RANDBETWEEN() formula within a LOOKUP formula | Excel Discussion (Misc queries) | |||
Lookup Formula | Excel Worksheet Functions | |||
Lookup Formula | Excel Worksheet Functions | |||
Lookup Formula - but have a formula if it can't find/match a value | Excel Worksheet Functions | |||
Lookup formula | Excel Discussion (Misc queries) |