| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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? |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return cursor to previous position | Excel Discussion (Misc queries) | |||
| return previous date from list | Excel Worksheet Functions | |||
| Return Path to previous cell | Excel Discussion (Misc queries) | |||
| Return to a previous cell | Excel Discussion (Misc queries) | |||
| How do I return to a previous worksheet in Excel? | Excel Discussion (Misc queries) | |||
Hybrid Mode
