VLOOKUP - when multiple matches on test value, can vlookup return highest cell value of in the relat
Posted by Russell on July 23, 2001 2:46 PM
This is a little difficult to explain, so, example data structure below:
Worksheet "Two" (vlookup table):
A B C
107301 201000 DAVID
107301 202000 BILL
108401 301000 JOHN
108401 302000 ROB
108401 303000 KEN
I would like vlookup (in Worksheet "One" below) to match on Column A as the test value, but, when matched (since there are multiple rows), look for the highest value in Column B, and return the value in Column C. For instance, my working sheet (Worksheet "One" below) would have only the Column A data with no duplicate values, with the objective being to the worksheet to use vlookup to return Column C, like this:
A C <--- vlookup to Worksheet "Two"
Keep in mind, there is never a 1-to-1 correlation between the value in Column B and Column C in the Worksheet "Two" vlookup table, thus the need to check the highest value of Column B and return the value in Column C.
You all's help will be greatly appreciated.