LOOKUP return the value from the previous row
 

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?

LOOKUP return the value from the previous row
 

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

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


"Steve" wrote:

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?

LOOKUP return the value from the previous row
 

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

If the names are unique:

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

--
Biff
Microsoft Excel MVP

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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?

LOOKUP return the value from the previous row
 

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.

"T. Valko" wrote:

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

If the names are unique:

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

--
Biff
Microsoft Excel MVP

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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?

LOOKUP return the value from the previous row
 

"Steve" wrote:

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" wrote:

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

If the names are unique:

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

--
Biff
Microsoft Excel MVP

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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?

LOOKUP return the value from the previous row
 

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.


--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...


"Steve" wrote:

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" wrote:

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

If the names are unique:

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

--
Biff
Microsoft Excel MVP

--
Biff
Microsoft Excel MVP


"Steve" wrote in message
...
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?