Please help! How can I use Lookup() command if values are not in ascending order?

[rruzicka]

Hi,

This is driving me insane. The lookup() command is perfect for what I
need, but the values will not be in ascending order.

example:

Result Lookup1 Lookup2
3200 4.112 4.066
3210 4.256 4.111
3220 4.221 4.525
3230 4.250 4.232


I want to be able to lookup value of 4.256 in lookup column 1 and have
excel return a value of 3210 for me. This will be followed by looking
up 4.525 in lookup column 2 and have excel return 3220 to me. Excel
doens't seem to want to do this unless the lookup columns are in
ascending order (this is not possible for my analysis). Please tell me
there is some way to do this???

 

[NH]

Assuming the value 4.256 is on cell E4 on cell F4 type
=INDEX(A1:A4,MATCH(E1,B1:B4))

 

[Don Guillett]

In the HELP index for VLOOKUP it talks about FALSE.
If range_lookup is TRUE, the values in the first column of table_array must
be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;
otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE,
table_array does not need to be sorted.

 

[rruzicka]

Thank you very much, Don! I missed the true/false part of the argument

 

[hgrove]

rruzicka wrote...

Thank you very much, Don! I missed the true/false part of the
argument.

You didn't miss anything. LOOKUP and VLOOKUP are different functions
and LOOKUP doesn't have a 4th argument. Only if you had checked the Se
Also link in the online help page for LOOKUP would you have seen mentio
of VLOOKUP.

That said, Don is correct about using VLOOKUP instead, but he shoul
have noted that it's a different function