**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula that will compare multiple columns, and return the column header name of whichever column had the highest value. I am using (INDEX($F$1:$K$1,0,MATCH(MAX(F2:K2),F2:K2,0))) , but one row had all the same values and the formula returned the first column header as being the "winner". This formula cannot distinguish when there are ties.

Solved by M. C. in 51 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
31/07/2018 - 06:31

Hi

User
31/07/2018 - 06:31

hello!

Excelchat Expert
31/07/2018 - 06:32

Hi, so I understood your problem of not being able to return the correct column when there is tie. Is that correct? Can you share your excel file?

Excelchat Expert
31/07/2018 - 06:32

What is your expected answer?

User
31/07/2018 - 06:33

let me type up an example of the data problem since I can't share my actual file

Excelchat Expert
31/07/2018 - 06:33

sure

User
31/07/2018 - 06:33

I'd expect an #N/A response

Excelchat Expert
31/07/2018 - 06:35

What if two columns have same highest value? Do you still want #N/A or some other sort of indication?

User
31/07/2018 - 06:35

yeah that's the problem I have. I'd want a "tie" indication

User
31/07/2018 - 06:35

maybe returning both candidate names

Excelchat Expert
31/07/2018 - 06:36

In your real data set you have three columns or more than that?

User
31/07/2018 - 06:36

more than that

Excelchat Expert
31/07/2018 - 06:36

Let me try to figure out a solution and get back to you.

User
31/07/2018 - 06:37

Ok thank you

User
31/07/2018 - 06:38

I'm going to update this example sheet so the formula is the exact same as the one I am working with

Excelchat Expert
31/07/2018 - 06:38

ok

Excelchat Expert
31/07/2018 - 06:42

Have you updated it?

User
31/07/2018 - 06:42

yes

Excelchat Expert
31/07/2018 - 06:42

Sorry for the same question again, how many columns you have in your dataset?

User
31/07/2018 - 06:43

I adjust it for each dataset I have, but the maximum I have is ten

Excelchat Expert
31/07/2018 - 06:50

So the solution I have adopted is quite simple. Let me share with you

User
31/07/2018 - 06:50

yay great!

Excelchat Expert
31/07/2018 - 06:51

However, I have written only to accommodate three columns but more can be added

[Uploaded an Excel file]

User
31/07/2018 - 06:52

i'll open it now

Excelchat Expert
31/07/2018 - 06:52

Sure

User
31/07/2018 - 06:54

so in order to add more columns should I edit and repeat this part IF(B4=MAX(B4:D4),B$3,"")

Excelchat Expert
31/07/2018 - 06:54

Yes

User
31/07/2018 - 06:55

ok let me try it out on my spreadsheet

Excelchat Expert
31/07/2018 - 06:55

Did you understand what the function is doing?

Excelchat Expert
31/07/2018 - 06:55

Let me know I can help you with the explaination

User
31/07/2018 - 06:55

I think it individually finds if that cell is the maximum value in the row, it returns the header name

User
31/07/2018 - 06:56

so it can return multiple header names

Excelchat Expert
31/07/2018 - 06:56

Yes

Excelchat Expert
31/07/2018 - 07:00

Hey, are you there?

User
31/07/2018 - 07:03

yes

User
31/07/2018 - 07:03

so i edited it for my sheet and seemed to have problems

User
31/07/2018 - 07:03

some of them just return #VALUE!

Excelchat Expert
31/07/2018 - 07:06

Can you show with dummy values in the sheet here?

User
31/07/2018 - 07:07

I put it on Sheet 2

Excelchat Expert
31/07/2018 - 07:07

Also, you will have to change max(B4:D4) to accommodate all the columns

User
31/07/2018 - 07:07

I did adjust it to F2:K2

User
31/07/2018 - 07:08

Maybe I messed up on commas or ""

Excelchat Expert
31/07/2018 - 07:08

Let me check

Excelchat Expert
31/07/2018 - 07:10

Yes, there is ( in before IF(I2=MAX(F2:K2)

Excelchat Expert
31/07/2018 - 07:12

I have made changes In L2

User
31/07/2018 - 07:13

ok thank you! let me try that out

Excelchat Expert
31/07/2018 - 07:16

:)

User
31/07/2018 - 07:16

In row 11 it returns that everyone tied, but since there is no data I would prefer a response like NA or even an Error response. Is there a way to work around rows with no data?

Excelchat Expert
31/07/2018 - 07:20

Give me a minute

User
31/07/2018 - 07:20

ok thanks

Excelchat Expert
31/07/2018 - 07:20

Done

User
31/07/2018 - 07:21

that's perfect!

Excelchat Expert
31/07/2018 - 07:21

I hope I was helpful in the session. This session will end in 10 minutes

Excelchat Expert
31/07/2018 - 07:21

:)

Excelchat Expert
31/07/2018 - 07:21

Great

Excelchat Expert
31/07/2018 - 07:21

Can you please give review at the end of this session? :)

User
31/07/2018 - 07:22

yes I will do that! Thank you for helping me solve that problem

User
31/07/2018 - 07:22

have a good day!

Excelchat Expert
31/07/2018 - 07:22

awesome

Excelchat Expert
31/07/2018 - 07:22

you too !

Excelchat Expert
31/07/2018 - 07:22

You can click on "End Session" to end the session

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*