Using index to lookup over large area

----A-----B-----C-----D---
1--matt--mark--luke--john
2--500---300---650---150-
3--432---424---456---124-
4--numbers here must not be included
5--354---242---853---123-
6--654---456---353---242-
7
8--=INDEX(A1:D1,MATCH(MAX(A2:D3,A5:D6),A2:D3,A5:D6,0))

in A8 in my code. Why does it no display the name of the person with highest score in this case luke with 853.

Can you tell me the code to enter into A8 to display the name of luke. The numbers in row 4 must now be included.

Originally Posted by liamthebof

Why does it no display the name of the person with highest score in this case luke with 853.

Can you tell me the code to enter into A8 to display the name of luke. The numbers in row 4 must now be included.

I assume you mean that the numbers in row 4 must noT be included? If so then try this formula

=INDEX(A1:D1,MIN(IF((A2:D6=MAX(A2:D3,A5:D6))*(ROW(A2:D6)<>4),COLUMN(A2:D6))))

confirmed with CTRL+SHIFT+ENTER

The reason your formula doesn't work is that MATCH function has to have a vector as the second argument, i.e. a single column or row range or array.

Note: with the formula I suggested if there is a tie it will return the leftmost name of the tiees

For your second question try

="Highest total is "&DOLLAR(MAX(B82:O82),0)&" by "&INDEX(B1:O1,MATCH(MAX(B82:O82),B82:O82,0))

Thank-you very much. You have helped me greatly. Both of the above formulas worked exacly as I wanted them to.

Respects,
Liam

the second code using dollar gives me the commas i need but adds a Pound symbol to frount of number, any way to get dollar symbol.