Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up the value corresponding to the last repetition in a ran
Essentially, I need to do a reverse-lookup; rather than finding the first
instance in a range, I need to find the last. My ColA contains a list of twenty repeating names, and ColB contains a number corresponding to each instance of a name. I'm trying to look up the value in ColB corresponding to the last instance of a given name. For instance, if my columns are such: ColA | ColB Bob | 1 Steve | 12 Mary | 41 Bob | 68 Mary | 3 Mary | 15 Steve | 72 Bob | 9 .... and I provide "Bob", I'd like to return the value 9; if I provide "Mary", I'd like to return the value 15. Thanks! Cell C1 will hold the name I'm providing. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Looking up the value corresponding to the last repetition in a ran
Try this:
=LOOKUP(2,1/(A2:A9="Bob"),B2:B9) -- Biff Microsoft Excel MVP "PaladinWhite" wrote in message ... Essentially, I need to do a reverse-lookup; rather than finding the first instance in a range, I need to find the last. My ColA contains a list of twenty repeating names, and ColB contains a number corresponding to each instance of a name. I'm trying to look up the value in ColB corresponding to the last instance of a given name. For instance, if my columns are such: ColA | ColB Bob | 1 Steve | 12 Mary | 41 Bob | 68 Mary | 3 Mary | 15 Steve | 72 Bob | 9 ... and I provide "Bob", I'd like to return the value 9; if I provide "Mary", I'd like to return the value 15. Thanks! Cell C1 will hold the name I'm providing. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random number with no repetition? | Excel Discussion (Misc queries) | |||
repetition cases | Excel Discussion (Misc queries) | |||
PivotTable - Repetition of rows | Excel Discussion (Misc queries) | |||
check for repetition of cell contents | Excel Worksheet Functions | |||
statistical analysis / trend repetition | Excel Discussion (Misc queries) |