CODEWITHSUNDEEP
excel
Steve
Steve

Reputation: 229

Finding Value In Excel Based On Highest Value and Matching Value in Excel

I have three rows in an Excel sheet that I need to get values from. Row 1 are just set numbers, Row 2 is values, and Row 3 is another value with many having the same value. I need to first check for the highest value in Row 3, match it with the value in Row 2, get the highest value in Row 2 first if Row 3 is the same, and then report Row 1 as the answer.

1   2   3   4   5   6   7   8   9   10  11  12
15  16  17  18  19  20  21  22  23  24  25  26
4   4   2   1   5   7   4   3   1   5   3   3

So I want to have it output as:

6 10 5 7 2 1 12 11 8 3 9 4

I tried a few HLOOKUP values but nothing seems to be hooking. Any ideas?

Upvotes: 1

Views: 71

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

For a formula answer use this array formula:

=INDEX($A$1:$L$1,MATCH(LARGE(($A$3:$L$3*10000)+$A$2:$L$2,COLUMN(A:A)),($A$3:$L$3*10000)+$A$2:$L$2,0))

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting Edit mode. If done correctly then Excel will put {} around the formula.

So enter the formula in the first cell, hit Ctrl-Shift-Enter Then drag/Copy across the number of columns desired.

enter image description here

Upvotes: 4

Related Questions