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:
Worksheet "One":
A C <--- vlookup to Worksheet "Two"
107301 BILL
108401 KEN
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.
Russell
Posted by Mark Weisman on July 23, 2001 2:51 PM
Use DMAX (in your criteria) and DGET.
Posted by Aladin Akyurek on July 23, 2001 3:17 PM
Russell,
This is an attractive question.
On Sheet1, in B1 array-enter:
=VLOOKUP(MAX((LVALUES=A1)*(KEYS)),LTABLE,2,0)
where LVALUES is the name given to A1:A5, KEYS to B1:B5, and LTABLE to B1:C5 (all via the Name Box).
In case a lookup value is not listed on Sheet2, use the following array formula instead:
=IF(ISNUMBER(MATCH(A12,LVALUES,0)),VLOOKUP(MAX((LVALUES=A12)*(KEYS)),LTABLE,2,0),"")
Note. In order to array-enter a formula (considering you'll not be the only reader of this post), hit CONTROL+SHIFT+ENTER at the same time (instead of just ENTER) to enter it.
Aladin
==============
Posted by Mark W. on July 23, 2001 3:26 PM
Here's the Dfunction solution...
Suppose that cells A1:C6 contains...
{"Field1","Field2","Field3"
;107301,201000,"DAVID"
;107301,202000,"BILL"
;108401,301000,"JOHN"
;108401,302000,"ROB"
;108401,303000,"KEN"}
...and that cells E1:F2 (the criteria) contained...
{"Field1","Field2"
;107301,202000}
where the value in E2, 107301, was entered by you
and the value in F2 was produced with the formula,
=DMAX($A$1:$C$6,"Field2",$E$1:$E$2). Now all you
have to do is use the formula,
=DGET($A$1:$C$6,"Field3",$E$1:$F$2)
...to produce your results, "BILL".
Posted by Russell on July 24, 2001 6:35 AM
Re: VLOOKUP - multiple matches solution - Thanks to Aladin and Mark
Thanks to Aladin and Mark for your solutions. So far I have used Aladin's solution of array-entering (ctrl-shift-enter) the following formula:
=IF(ISNUMBER(MATCH(A12,LVALUES,0)),VLOOKUP(MAX((LVALUES=A12)*(KEYS)),LTABLE,2,0),"")
It worked great! Thanks so much for your solutions.
Russell
