LOOKUP return the value from the previous row

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Im using the LOOKUP functions a couple of times in a workbook and it works
correctly in all places except one. Here's an example of what is happening:

A B
1 Jane 20
2 Jack 30
3 Stan 40
4 Dave 50

=LOOKUP(Stan, A:A, B:B)

In most places in my spreadsheet the result would be 40. BUT... in one
particular place the result is 30. Can someone help please?
 
The LOOKUP function is required sorted in ascending order. Try VLOOKUP instead

=VLOOKUP("Stan",A1:B100,2,0)
 
LOOKUP requires the lookup_vector be sorted in ascending order to work
properly.

If the names are unique:

=SUMIF(A:A,"Stan",B:B)
 
I apologize, bad example. Names are in ascending order.

A B
1 Dave 50
2 Jack 30
3 Jane 20
4 Stan 40

It works 9/10 times for some odd reason.
 
Steve said:
I apologize, bad example. Names are in ascending order.

A B
1 Dave 50
2 Jack 30
3 Jane 20
4 Stan 40
Click to expand...

=LOOKUP(Stan, A:A, B:B)

Expecting 40 but getting 20. It works 9/10 times for some odd reason.
 
I was able to reproduce your problem if there was a trailing space after
Stan:

Dave...50
Jack...30
Jane...20
Stan<space>...40

=LOOKUP("Stan", A:A, B:B)

Returned 20 which is correct based on how LOOKUP works but of course that's
not the result you're expecting.

With the lookup_value being Stan and there not being an *exact* match with
Stan<space> the formula looks for the closest value that is less than the
lookup_value. In this case that value is Jane.
 
Back
Top