Formula for finding the column name?

paigemcd

I have a set of data where I am trying to do 2 things:

1. Find the top 5 highest values in a row. I used a LARGE formula to find the values in the 1st-5th highest spots (seen in rows 9-11).
2. Find the name of the columns where the 5 largest values reside, without having to manually look at them. This is the one I can't get to work. (rows 14-16).

I was hoping that after finding those two data points I could concatenate the two into one to share on my dashboard. The goal is that it would combine 1 and 2, so for Jenny it would show "Banana 17, Apple 14, Pear 7, etc…". Please help if you can think of a way to find my #2. If it can't find a column name, is there another way you can think of?

Cory Page

I don't believe there is a way to pull down the column name using any formula. The easiest option would be to use your top row as a second column header then use that to populate your #2 details. I would think you could use your #1 results to pull in the #2 results after that. Not sure if you want to have a redundant row in your sheet or not.

paigemcd

@Cory Page thank you for the help! What formula would you suggest for finding the cell name that is vertically adjacent to the highest value? Or second, third highest etc. In this case - how can I get cell "Apple 15" to say Banana? Since Banana is the highest value (17) in the row Jenny