MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Calculate and Display the Largest Value


March 01, 2002 - by Juan Pablo Gonzalez

Jon asks:

I am using the the Max function, and have a table for which i want to show the largest value. I do not want the actual value to be shown though, i want the corresponding title to be shown how do I do this?

The Table looks like this(though on a larger scale):

C ... Q
1 Crisps ... 100
2 Chocolate ... 345
3 Sweets ... 54

How do i get it (without using IF as the table is much too big) to show for the maximum value 'Chocolate' instead of '345'?

If you need multiple IF's, chances are that you're using the wrong formula!!... try with this one instead:

=INDEX(C:C,MATCH(MAX(Q:Q),Q:Q,0))