LOOKUP return the value from the previous row

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

 

[Guest]

The LOOKUP function is required sorted in ascending order. Try VLOOKUP instead

=VLOOKUP("Stan",A1:B100,2,0)

 

[T. Valko]

LOOKUP requires the lookup_vector be sorted in ascending order to work
properly.

If the names are unique:

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

 

[Guest]

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.

 

[Guest]

I apologize, bad example. Names are in ascending order.

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

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

Expecting 40 but getting 20. It works 9/10 times for some odd reason.

 

[T. Valko]

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.