# Reverse 2D Lookup

#### juanito

##### Member
I have a table of values with headings along the top and down the left-hand side. Months and soccer teams respectively, for instance.

I need a formula which returns both headings of the largest value. Thought this would be easy-ish, but I'm having a bad day...

Any Forum help will be much appreciated.

- Juanito

#### Luke M

##### Excel Ninja
Method might be similar to this idea:

Instead of returning an address though, we can take the arguements that were giving us the row and column and use that to give us the values you want.

=INDEX(1:1,MID(MAX(IF(B2:D10=MAX(B2:D10),VALUE(900&TEXT(COLUMN(B2:D10),"000")&ROW(B2:D10)))),4,3))

Labels in col A:

=INDEX(A:A,MID(MAX(IF(B2:D10=MAX(B2:D10),VALUE(900&TEXT(COLUMN(B2:D10),"000")&ROW(B2:D10)))),7,999))

Both of these are array formulas and should be confirmed using Ctrl+Shift+Enter, not just Enter.

#### juanito

##### Member
Amazing, Luke: thanks!

- Juanito

#### Luke M

##### Excel Ninja
No problem, Juanito. Always glad to help. =)

#### Sajan

##### Excel Ninja
Hello,

Excel gives you lots of choices for a solution... so here is one more approach:

Assuming that your data is named "ScoresData"

The top header for the largest score is:

=INDEX(\$1:\$1,MAX((ScoresData=MAX(ScoresData))*COLUMN(ScoresData)))

entered with Ctrl + Shift + Enter

The left header for the largest score is:

=INDEX(\$A:\$A,MAX((ScoresData=MAX(ScoresData))*ROW(ScoresData)))

entered with Ctrl + Shift + Enter

Cheers,

Sajan.