All solutions INDEX Expert Solution – Excel INDEX Problems

Excel - INDEX Function Problem - Expert Solution

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc