Use MATCH but with 2 identical values in Sheets

Question

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))  

Answer 1

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.

Answer 2

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).