How do I get Excel to return a value based on the max value in a table?

Question

In the table the largest value is 1.71, how do i return -2.25 and 235?

Answer 1

It appears from your picture that you need a two-dimensional lookup. Given a value within a table, retrieve both the column header and row header associated with that value. Consider the following simple example:

We want the name and topic associated with the highest score. The highest score in the example is 100; so we want to retrieve Jim and Spelling

In A7 enter:

=MAX(B2:F5)

This is our lookup value. In B8 we enter the array formula:

=INDEX($B$1:$F$1,1,MIN(IF($B$2:$F$5=A7,COLUMN($B$2:$F$5)-COLUMN($B$2)+1)))

In B9 enter the array formula:

=INDEX($A2:$A$5,MIN(IF($B$2:$F$5=A7,ROW($B$2:$F$5)-ROW($B$2)+1)),1)

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

The result: