Assumptions:
A3:A5 contains the name
D3:D5 contains the total sold
Formulas (confirmed with CONTROL+SHIFT+ENTER, not just ENTER):
F3, copied down:
=INDEX(A$3:A$5,MATCH(LARGE($D$3:$D$5-ROW($D$3:$D$5)/10^10,ROWS(F$3:F3)),$
D$3:$D$5-ROW($D$3:$D$5)/10^10,0))
G3, copied down:
=INDEX(D$3:D$5,MATCH(LARGE($D$3:$D$5-ROW($D$3:$D$5)/10^10,ROWS(G$3:G3)),$
D$3:$D$5-ROW($D$3:$D$5)/10^10,0))
Hope this helps!
In article ,
"G" wrote:
I'm referencing a range and creating an automatically-sorted list using
VLOOKUP, sorting by the highest total sold. Here's my reference information:
Column A B C D E
Name Week 1 Week 2 Total Sold (helper)
Widget 1 3 3 6 =A3
Widget 2 2 3 5 =A4
Widget 3 2 3 5 =A5
Here's the code:
Column A Column B
=VLOOKUP(F2,D$3:E$5,2,FALSE) =LARGE(D$3:D$5,1)
=VLOOKUP(F3,D$3:E$5,2,FALSE) =LARGE(D$3:D$5,2)
=VLOOKUP(F4,D$3:E$5,2,FALSE) =LARGE(D$3:D$5,3)
My results are as follows:
Column F Column G
Widget 1 6
Widget 2 5
Widget 2 5
The problem is that WIDGET2 is referenced twice because the totals for
Widget 2 and Widget 3 are identical (5) and, apparently, the first referenced
field is displayed.
Can anyone show me how I can code this to DYNAMICALLY display the correct
sort order/fields?
Thanks.
Gary
|