Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have 4 field of Numeric Values, I want Excel To pick the highest value of them and show me the name of the field that is related to the highest one, for example:

Option 1 (J10)     Option2 (K10)     Option3(L10)     Option4(M10)

and the values are :

50(J15)          60(K15)            70(L15)          65(M15)

I want Excel to return ( Option 3) instead of showing me the number 70

share|improve this question

migrated from stackoverflow.com Aug 29 '12 at 20:24

This question came from our site for professional and enthusiast programmers.

1 Answer 1

up vote 2 down vote accepted

something like

=INDEX(J10:M10,MATCH(MAX(J15:M15),J15:M15,0))

share|improve this answer
    
I use the combination of INDEX & MATCH all the time when looking up disparate columns (or rows) of data. Microsoft should make a variation of VLOOKUP & HLOOKUP that handles this kind of thing. It would be much easier. –  Enigmativity Aug 28 '12 at 4:21
    
I suppose LOOKUP is close enough to that except that it assumed the data is sorted –  brettdj Aug 28 '12 at 5:41
    
The VLOOKUP command does not require the data to be sorted as long as you put FALSE as the last parameter. –  Enigmativity Aug 28 '12 at 6:28
    
Yes, but I was referring to LOOKUP. –  brettdj Aug 28 '12 at 7:16
    
Oops, yes, my mistake. –  Enigmativity Aug 28 '12 at 7:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.