# Use MATCH but with 2 identical values in Sheets

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

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.

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

• 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