Screwy lookup function

  • Thread starter Thread starter Matt
  • Start date Start date
M

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??
 
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
 
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))
 
Back
Top