HLookup

I have a 6-column table and I'd like to output the column header for each row of the max-value column.    Ok, that may sound confusing... here's an example:


A     B     C     D     E     F           Best
98   97   88   99    76   81        D
92   93   91   92    95   97        F
98   89   88   97    96   98        A, F

Only challenge maybe the 3rd row where A and F have the same max value.  

How can this be accomplished (via HLookup)?

EEH
ExpExchHelpAsked:
Who is Participating?
 
NBVCConnect With a Mentor Commented:
You can try this formula:  

Assuming data is in A:F, then in G2 try:

=SUBSTITUTE(TRIM(IF(A2=MAX(A2:F2),A$1,"")&" "&IF(B2=MAX(A2:F2),B$1,"")&" "&IF(C2=MAX(A2:F2),C$1,"")&" "&IF(D2=MAX(A2:F2),D$1,"")&" "&IF(E2=MAX(A2:F2),E$1,"")&" "&IF(F2=MAX(A2:F2),F$1,""))," ",", ")

copied down.
0
 
Saqib Husain, SyedEngineerCommented:
Hlookup will only return one. For more than on you probably have to go to VBA
0
 
ExpExchHelpAuthor Commented:
NB_VC:

That's the PERFECT solution!!!!

Thousand thanks,
EEH
0
 
ExpExchHelpAuthor Commented:
Perfect Solution!!!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.