2
A       B      C       D         E
-----------------------------------
foo    bar    test    foobar
-----------------------------------
10     13      3       1      bar
-----------------------------------
9      3       3       9       ?

I am trying to identify the highest number of results in my survey and have it appear in column E. However, I can't figure out how make a tie appear in column E. It doesn't matter whether it takes multiple columns to get what I want. Here is the formula I used for the first row of data.

=INDEX(A$1:D$1, 1, MATCH(MAX(A2:D2), A2:D2, 0))  
2

Short answer

Try

=ArrayFormula(
  substitute(trim(query(transpose(IFERROR(
    HLOOKUP(IF(A2:D2=MAX(A2:D2),COLUMN(A2:D2),),{COLUMN($A$1:$D$1);$A$1:$D$1},2,FALSE),
  )),,2000000))," ",",")
)

Explanation

MATCH can only return a single value, so another thing should be used. A formula with nested functions is proposed:

  • IF(A2:D2=MAX(A2:D2),COLUMN(A2:D2),) compares each value with the maximum value, if they are equal, then returns the column number, if not, returns FALSE.
  • HLOOKUP looks the previous result in {COLUMN($A$1:$D$1);$A$1:$D$1}. This is an array having two rows, the first is the column numbers, the second the column headers. If there is a match it will return the column header, otherwise an #N/A!.
  • IFERROR(previous_result,) if its first argument is a value, returns the value, otherwise in this case returns a blank.
  • substitute(trim(query(transpose(previous_result))," ",",") is used to concatenate the results, in this case, by using a comma as separator.
2

enter image description here

Using the same data set you had above, =JOIN(",",FILTER(TRANSPOSE($A$1:$D$1),TRANSPOSE($A3:$D3)=MAX($A3:$D3)))
accomplished what you are asking for.

The TRANSPOSE() formula is only because I wanted to use the FILTER() formula.
The FILTER() formula is particularly useful because one can set the criteria as the MAX() of a particular row, and use the JOIN() formula to combine results into on cell using a delimiter (like a comma).

  • 1
    I fixed it. Should Apply now. It was correct but I added a row & column to make pseudo-headers for the picture, and it messed up my references. – Xzila Sep 23 '16 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.