Help with Excel formula

Question

Hello,

Please see picture below. I have a MAX function in cell B20 that finds the highest average of any average in row 17 of the columns from A-J. I would like to know what formula to enter in cell B21 that will output the text in row 1 cell for the column that has the highest average. For example, in the data below, the formula in B21 would look into cell B20, find "590.47" which is the highest average in row 17, which is in cell G17, then return whatever text is in row 1 of column G. (Which is "how is x7" in cell G1)

When the numbers in rows 2 through 16 change, the averages in row 17 would change, and the max in cell B20 would update. So then I would want cell B21 to show whatever is the text in row 1 of the column with the new highest average. Thank you!!

Answer 1

You may try this one to get the Highest value directly.

=INDEX(A1:J1,LET(b,BYCOL(A2:J16,LAMBDA(a,(SUM(a)))),MATCH(MAX(b),b,0)))

Answer 2

Hi,

Try this formula

=xlookup(B20,A17:J17,A1:J1)

Hope this helps.

Answer 3

Hi Craig. I am an Excel user like you.

Here is a formula that will enter the header from Row 1 of the Column with the Highest value from your MAX function. Enter this in B21.

=INDEX(A1:J1,1,MATCH(B20,A17:J17,0))

.

Answer 4

I just received 3 great answers from you experts!!

thanks very much for your great assistance!!

it's nice to know there is help out there when needed!!

Craig