Return Persons Name from Highest Value in other column

Are you happy for a slightly different layout? If so I can provide a solution.

Please be aware, if you are asking your staff to rank themselves in this way, that there will be a gender bias within a representative sample group of people: men are far more likely to award themselves the highest ranking, whereas women who are in fact just as skilled will not be so likely to do so, more often choosing the second highest ranking.

Here's my suggestion:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K
16		Question 1	Question 2	Question 3	Question 4	Question 5	Question 6	Question 7	Question 8	Question 9	Question 10
17	Max Value	4	4	3
18		Person 3	Person 4	Person 2
19				Person 4
20

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	B
18	=IFERROR(INDEX($A:$A,SMALL(IF(B$2:B$5=B$17,ROW($A$2:$A$5)),ROWS($A$1:A1))),"")

Sheet: Sheet1

... confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Once confirmed, copy across and down.

Why would it be tricky to lay it out this way? Google Sheets are spreadsheets just like Excel spreadsheets - you can lay them out as you choose.

It doesn't matter how many women there are: it is a proven fact that men will be far more confident in their abilities, so you will still get more men ranking themselves top. I'm just saying this because I think your system, based on their own assessment of their ability, is probably flawed.

One more thing - there is a separate section for platforms other than Excel, so I'm moving this thread to it. If you are using something else, you should always state this in your opening post, however, I can't see in this instance how it would make a difference other than the way that the array formula might need entering. That I can't help you with as I do not use Google Docs.

Hang on a minute - you said this:

The data comes from a Google Sheet so it would be tricky to lay it out in the way you suggest...

But you are now saying that you are working in Excel. So why would it be tricky? I'll move it back, but I would like to understand what you mean by this.

The table I have created is not a replacement for your data - it's the solution and appears below your data.

See the sample file attached, which shows you what I've done.

Does it really need to be in one cell? What's wrong with a list of people?

I'm afraid I don't have the answer - maybe someone else can help.

Sorry, but isn't that what I've given you? I really don't understand the need for multiple results to be in the same cell. I don't think the governors at my school, all being high-fliers in their respective fields, would have any trouble in understanding the data as it is presented in my solution. Is this a case of form over function, I wonder?

I still question whether your assessment of your staff is really empirical: is this being presented as fact (i.e. these four members of staff are capable of doing x), or are you presenting the data with the caveat that this is based on self-assessment?

Sorry - I know, dog with a bone ...

OK - this makes more sense. You can use CONCATENATE to bring the results in my output table together in your final document, or you can tweak and use my solution in your final output, but create it such that the output box spans several rows, or just wait for someone else to come up with a solution that does it in one go. As for me, well I'm on summer holiday already and a sunny garden is beckoning.