LOOKUP returning some incorrect values

[jeff]

I have a problem that has appeared in a large spreadsheet where the Lookup
function returns some values correct and not others. To test what was
happening I did a small spreadsheet to simply match two sets of numbers. it
works for about half the column down to G17 and then goes crazy. It does get
the last result correct! The result column should just be a list of
descending numbers - the reverse of the data columns . The test data is below:
Result
G1 1 G34 34
G2 2 G33 33
G3 3 G32 32
G4 4 G31 31
G5 5 G30 30
G6 6 G29 29
G7 7 G28 28
G8 8 G27 27
G9 9 G26 26
G10 10 G25 25
G11 11 G24 24
G12 12 G23 23
G13 13 G22 22
G14 14 G21 21
G15 15 G20 20
G16 16 G19 19
G17 17 G18 18
G18 18 G17 17
G19 19 G16 1
G20 20 G15 1
G21 21 G14 1
G22 22 G13 1
G23 23 G12 1
G24 24 G11 1
G25 25 G10 1
G26 26 G9 34
G27 27 G8 34
G28 28 G7 34
G29 29 G6 34
G30 30 G5 34
G31 31 G4 34
G32 32 G3 29
G33 33 G2 19
G34 34 G1 1

I am sure it is something simple but it is driving me crazy - any suggestions?
Thanks

 

[jeff]

Oops, yes i should have included the formula. Yes it does use absolute
references as follows: =LOOKUP(D3,$A$3:$A$36,$B$3:$B$36)

 

[CyberTaz]

What would have helped more than your *results* would be a copy of the
*formula* that is returning them, but lacking that let me guess - Does the
lookup range (table) in the formula use absolute references, i.e.:

$G1$:$G$45

as opposed to just using relative references:

G1:G45

If not, the relative references are changing as the formula is copied down &
soon runs out of the table range.

 

[jpdphd]

Oops, yes i should have included the formula. Yes it does use absolute
references as follows: =LOOKUP(D3,$A$3:$A$36,$B$3:$B$36)

Jeff,
I think if you replace G1 with G01, (etc thru G9), it will work
correctly. What puzzles me is that I would have thought that the wrong
answers would have begun with G10 rather than G16. Maybe there's some
hexidecimal conversion silently working in the background.
jpdphd

 

[Bob Greenblatt]

Jeff,

It is working ABSOLUTELY correctly, and as expected. What is happening is
that your input array, the stuff in A3:A36 is not is ascending order as
REQUIRED by the lookup function. You think they are in order, but they
aren't. To do what you want, the values in column A must be G01, G02, or G 1
G 2, etc. the lookup function is returning the correct matching value for
the next lower value. Read the Help discussion carefully.

If you do not want to change the values in A3:A11, use the index and match
functions: =INDEX($B$3:$B$36,MATCH(D3,$A$3:$A$36,0))

 

[jeff]

--

Jeff,
I think if you replace G1 with G01, (etc thru G9), it will work
correctly. What puzzles me is that I would have thought that the wrong
answers would have begun with G10 rather than G16. Maybe there's some
hexidecimal conversion silently working in the background.
jpdphd

Thanks for that, it does fix the problem. I thought it was something to do
with G11 having two ones, as this was where it was tripping up in the main
spreadsheet although it got past this in the test spreadsheet. I don't know
why this works but i can live with it - thanks again.

Jeff

 

[CyberTaz]

Hi Jeff -

If you happen to return this may help explain it:

Since you're dealing with a text string each character has a value
associated with it and Excel looks at the series of values, not the string
as a whole - IOW, when you look at G11 you see "Gee eleven", when Excel
looks at the same string it sees "Gee one one". Anything with a higher
single digit after the G (such as G2) is interpreted as a higher value than
any where the first digit after the G is a 1 (G1, G11, G111, ad infinitum).

If you're interested there is a table of ascii values at:

http://www.asciitable.com/

HTH |:>)
Bob Jones
[MVP] Office:Mac



On 8/17/07 5:53 PM, in article
(e-mail address removed), "jeff"
<[email protected]> wrote:

 

[jeff]

Yep, thanks all, I can see now how it thinks - problem solved!