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.
