Prev: Recover lost excel tabs/spreadsheets from workbook
Next: Protecting an excell workbook
From: EmilyE on 21 Apr 2010 12:08
I'm not sure if this would be a look up formula, but is there a formula where
I can type in a number in one cell and in the cell next to it, it will pop up
a description that i've written in another cell connected to that number on
another sheet?
From: Paul C on 21 Apr 2010 12:34
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?
From: trip_to_tokyo on 21 Apr 2010 12:51
EXCEL 2007

Please refer to:-

http://www.pierrefondes.com/

Item number 93.

In Sheet1 in cells A2 to A6 entry of any one of these numbers:-

200 / 400 / 500 / 600

- will return, respectively, any one of:-

60 / 10 / 10 / 20

- in cells B2 to B6.

The, "conversion table" is found on Sheet2.

Just amend the Workbook to suit your requirements.

If my comments have helped please hit Yes.

Thanks.












I'm not sure if this would be a look up formula, but is there a formula where
> I can type in a number in one cell and in the cell next to it, it will pop up
> a description that i've written in another cell connected to that number on
> another sheet?
From: Roger Govier on 21 Apr 2010 13:34
Hi Emily

Supposing that your entries are on Sheet2 with a number in column A and
a description alongside that number in column B.

On sheet1, you enter a number in cell A2, then paste this formula in B2
=IF(A2="","",VLOOKUP(A2,Sheet2!A:B,2,0)
Copy down as required
--
Regards
Roger Govier

EmilyE wrote:
> I'm not sure if this would be a look up formula, but is there a formula where
> I can type in a number in one cell and in the cell next to it, it will pop up
> a description that i've written in another cell connected to that number on
> another sheet?
From: Roger Govier on 21 Apr 2010 14:03
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)
>
>
 | 
Pages: 1
Prev: Recover lost excel tabs/spreadsheets from workbook
Next: Protecting an excell workbook