Screwy lookup function
 

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??

Screwy lookup function
 

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Matt" wrote in message
ps.com...
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??

Screwy lookup function
 

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 BCutmove 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))

--
Regards

Roger Govier


"Matt" wrote in message
ps.com...
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??