Lookup up in cells with formulas

  • Thread starter Jennifer Waterhouse
  • Start date
J

Jennifer Waterhouse

I'm having a problem with the Lookup function.
The cells that the lookup function is searching have a formula in them. (SUM)
This is buggering up the Lookup function. Is there a way for the Lookup function to overlook the formulas and just search the results or hide the formula in some way so the Lookup doesn't read them?

Thanks in advance
Jen
 
F

Frank Kabel

Hi
LOOKUP should work without a problem on formula results.
You may post your lookupm formula and your sum formula as well

--
Regards
Frank Kabel
Frankfurt, Germany

I'm having a problem with the Lookup function.
The cells that the lookup function is searching have a formula in them.
(SUM)
This is buggering up the Lookup function. Is there a way for the
Lookup function to overlook the formulas and just search the results or
hide the formula in some way so the Lookup doesn't read them?

Thanks in advance
Jen
 
J

Jennifer Waterhouse

Column A Column B Column C Column D

2641 blue
2782 green
2693 yellow

Formula in D1 is =lookup(C1, A:A, B:B)
In the cells for column A the cell formula is =sum (E1*F1)
If I enter '2782' in cell C1 it returns BLUE. Same for any entry.

Jen
 
M

Myrna Larson

Check help for LOOKUP. You'll see that it requires that the first column be
sorted ascending. The data you show is not sorted.

Instead of LOOKUP, use VLOOKUP with all 4 arguments:

=VLOOKUP(C1,A:B,2,0)

With the last argument =0, the data in column A doesn't need to be sorted.
 
F

Frank Kabel

Hi
1. Probably your data is stored as 'Text' and not as number. Does the
formula
=ISNUMBER(A1)
return TRUE. If not you have a formating issue

2. Change your formula to
=E1*F1
No need for SUM
 
J

Jennifer Waterhouse

Thanx Myrna. That worked.

Myrna Larson said:
Check help for LOOKUP. You'll see that it requires that the first column be
sorted ascending. The data you show is not sorted.

Instead of LOOKUP, use VLOOKUP with all 4 arguments:

=VLOOKUP(C1,A:B,2,0)

With the last argument =0, the data in column A doesn't need to be sorted.
Click to expand...
 
Top