Find two highest values and index (even value is the same)

D

diilmac

New Member
Joined
Apr 17, 2019
Messages
4
Hello

I tried many combinations of formulas but every time first and second highest value is the same im getting first key for both.

DATA
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]USER1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]USER1[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]USER1[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]USER2[/TD]
[TD]5[/TD]
[/TR]
</tbody>[/TABLE]


Display
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]#[/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][find key [DATA A:A] of first highest value [DATA C:C] based on [Display B3][/TD]
[TD]USER1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][find key [DATA A:A] of second highest value(even is the same as first one) [DATA C:C] based on [Display B3][/TD]
[TD]USER1[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Try:
Copy formulas down as needed.
Excel Workbook
ABC
11USER18
22USER18
33USER14
44USER25
5
6
7
8
91USER1
102USER1
Sheet
 
Upvote 0
In my post above formula in A9 doesn't need to be enter as an array (just ENTER will work).
 
Upvote 0
Thank you AhoyNC for quick answer, but this is not exactly what Im looking for.
Let me explain one more time

[TABLE="class: grid, width: 500"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]USER1[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]USER1[/TD]
[TD="align: center"]8[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]USER1[/TD]
[TD="align: center"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]USER2[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]USER3[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]USER2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]USER4[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]USER3[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]9[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]USER4[/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]10[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]USER2[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]11[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]USER3[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]13[/TD]
[TD][key (A column) of first highest value (C column) based on B13][/TD]
[TD]USER1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]14[/TD]
[TD][key (A column) of second highest value (C column) based on B14][/TD]
[TD]USER1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]15[/TD]
[TD][key (A column) of first highest value (C column) based on B15][/TD]
[TD]USER2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]16[/TD]
[TD][key (A column) of second highest value (C column) based on B16][/TD]
[TD]USER2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]17[/TD]
[TD][key (A column) of first highest value (C column) based on B17][/TD]
[TD]USER3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]18[/TD]
[TD][key (A column) of first highest value (C column) based on B18][/TD]
[TD]USER3[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Basically I need to find only key, B13:B18 will be fixed value
 
Upvote 0
Try:
This is an array formula that must be entered with CTRL-SHIFT-ENTER. Drag formula down as needed.
Excel Workbook
ABC
11USER18
22USER18
33USER14
44USER25
55USER31
66USER23
77USER47
88USER39
99USER42
1010USER211
1111USER312
12
13USER11
14USER12
15USER210
16USER24
17USER311
18USER38
Sheet
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
310
JMWh1t3
J
E
  • Solved
Ranking values (in descending order) with 1 criteria - BUT without skipping numbers
Replies
3
Views
275
Fluff
Fluff
I
  • Question Question
Find the second highest attribute based on criteria. I know its a combo of index(match(large, but I just can't get it to work correctly.
Replies
1
Views
504
StephenCrump
S
G
  • Solved
How To Compare Two Columns In Excel?
Replies
3
Views
390
Gigiwan
G
D
  • Question Question
Using VBA to determine Maximum Values in an Excel Spreadsheet
Replies
4
Views
470
Artik
Artik

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top