Lookup Problem

L

Linda Peters

I have a rather large workbook with multiple uses of LOOKUP over a variety of
worksheets. In some cases, the LOOKUP of a particular value occurs only on
one worksheet, in other cases LOOKUP of the same value may occur over several
spreadsheets.

The problem I've run in to today is that when the data is updated, the value
returned by LOOKUP may not be correct (it keeps the old value and doesn't
replace it with the new one). In this particular instance, I used LOOKUP on
two different worksheets, both looking up values from the same table; some of
the values being looked up were the same, some were different. Spreadsheet 1
updated to the new values when the data was changed, however Spreadsheet 2
did not and kept the old value (which is now incorrect).

I was able to correct it by "re-doing" the LOOKUP formula on Spreadsheet 2,
but that's not going to be very effective long term.

Any suggestions of what the problem might be?
 
N

Niek Otten

<Any suggestions of what the problem might be?>

Not without your formulas, input values, expected and actual results!
 
L

Linda Peters

Formula from spreadsheet 1: =LOOKUP(A27,Data!A:A,Data!L:L)
Expected result: 18165
Actual result: 18165

Formula from spreadsheet 2: =LOOKUP(A80,Data!A:A,Data!L:L)
Expected result: 18165
Actual result: 13665 (this is the old result, prior to updating the data)

In both cases, the value being looked up (in A27 & A80) are the same value
(an accounting code: 1-102-342-205-110)
 
K

Ken Wright

If you do Tools / options / calculation, does it say automatic?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
N

Niek Otten

<(this is the old result, prior to updating the data)>

Which data are you updating; A:A?

LOOKUP requires that the data is sorted ascending. Is that condition
satisfied?

LOOKUP is not used a lot anymore. As HELP says, it is provided for backward
compatibility.
Normally, one would use VLOOKUP.

BTW, did you check Tools>Options>Calculation Tab and make sure Calculation
is Automatic?
 
Top