Solved

# HLookup

Posted on 2012-09-17
308 Views
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
0
Question by:ExpExchHelp

LVL 43

Expert Comment

Hlookup will only return one. For more than on you probably have to go to VBA
0

LVL 23

Accepted Solution

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

Author Comment

NB_VC:

That's the PERFECT solution!!!!

Thousand thanks,
EEH
0

Author Closing Comment

Perfect Solution!!!!
0

## Featured Post

### Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …