If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
find LAST match in column
Hi Domenic,
I just saw this correspondence and I wonder if you could explain the formula because i do not understand how it can return the right value (even though it does!) For instance, how do the formula know that the input 1/A1:A1000"" refers to the last cell? Thanks Adam "Domenic" skrev: Yes, definitely! Thanks for the reminder, Harlan! Although, wouldn't 2 serve better as the lookup value? For example, take the following table... Bob a Jack b Jane c Bob d Jill e Phil f Bob g Jason h If you use... =LOOKUP(1,1/(A1:A100="Bob"),B1:B100) ...the correct result is returned, that being "g". However, if you use... =LOOKUP(1,1/(A1:A8="Bob"),B1:B8) ...an incorrect result is returned, that being "d". Using 2 as the lookup value returns the correct result regardless of the range one uses. |
#2
|
|||
|
|||
The formula extends a formula for finding the (position of) last numeric
value in a range. The extension is due to Harlan Grove. How it works is explained he http://tinyurl.com/7ysq5 Adam wrote: Hi Domenic, I just saw this correspondence and I wonder if you could explain the formula because i do not understand how it can return the right value (even though it does!) For instance, how do the formula know that the input 1/A1:A1000"" refers to the last cell? Thanks Adam "Domenic" skrev: Yes, definitely! Thanks for the reminder, Harlan! Although, wouldn't 2 serve better as the lookup value? For example, take the following table... Bob a Jack b Jane c Bob d Jill e Phil f Bob g Jason h If you use... =LOOKUP(1,1/(A1:A100="Bob"),B1:B100) ...the correct result is returned, that being "g". However, if you use... =LOOKUP(1,1/(A1:A8="Bob"),B1:B8) ...an incorrect result is returned, that being "d". Using 2 as the lookup value returns the correct result regardless of the range one uses. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 12:55 PM |
need to find which numbers (3+) in a column sum to a value | Devin | General Discussion | 1 | February 11th, 2005 11:30 PM |
How to sort/update large excel db | ConfusedNovice | General Discussion | 15 | February 2nd, 2005 01:43 AM |
Find value in column and associate result to it | Eduardo | General Discussion | 1 | June 17th, 2004 03:12 PM |
Find highest value in column | Don Guillett | Worksheet Functions | 1 | May 5th, 2004 01:37 PM |