Screwy lookup function

[Matt]

Not sure if I am doing something wrong but seem to be getting some
screwy results using the lookup function. Couple examples:

1. With this array:
Alpha 6
Beta 3
Gamma 6
Delta 3

=LOOKUP(3,C7:C10,B7:B10) -----> Beta (gives the first 3 value
in the list)
=LOOKUP(6,C7:C10,B7:B10) -----> Gamma (gives the second 6 value in
the list)

2. With this array:
Alpha 10
Beta 3
Gamma 6
Delta 3

=LOOKUP(10,C7:C10,B7:B10) ----> Delta (why didn't it give
alpha?)

3. With this array
Alpha 2
Beta 3
Gamma 6
Delta 3

=LOOKUP(10,C7:C10,B7:B10) ----> #NA (can't find a 2-value?)

What's going on here??

 

[Bernard Liengme]

From Help:
Important The values in lookup_vector must be placed in ascending order:
....,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give
the correct value. Uppercase and lowercase text are equivalent.

best wishes

 

[Roger Govier]

Hi Matt

Lookup only works with data that is sorted ascending, that is the reason
for your inconsistent results.

Try Vlookup instead, with the optional 4th parameter set to FALSE or 0
Note, however, that the value being looked up has to within the first
column of the range.
You would need to mark column B>Cut>move to column A.Insert cut cells
Then
=VLOOKUP(10,A1:B4,2,0)

If you didn't want to rearrange your columns, then use Index / Match
=INDEX(A1:A4,MATCH(10,B1:B4,0))