Find Highest Value In A Row, Return the column header - across sheets

Occasional Contributor

Hi

 

I'm trying to return the header that matches the highest value in a row but I'm having some trouble with my formula.

 

=INDEX(Sheet2!A1:F1,0,MATCH(MAX(Sheet2!A10:F10,Sheet2!A13:F13),Sheet2!A10:F10,Sheet2!A13:F13,0))

 

thanks

8 Replies

@cheeseontoast101 

 

Can you add an image of the array of data in Sheet2? It would help those of us who'd like to help you; otherwise we're not only having to diagnose a formula, but also visualize what it is that said formula is working on. Please, help us help you.

That's an amazing formula, @Sergei Baklan   I like exceljet and often use it; glad to see you do too. 

@mathetes 

Dave makes a great job, he systemises all around Excel. Not to explain this and that from scratch usually it's much better to give link on his resource.

@mathetes I can't provide the exact image as it's for working purposes.

 

Below is a similar example of what I'm trying to do. Basically in cell B4 on sheet 1 I want it to reference the header of the fruit that is the best producing in sheet 2. In the example below with the right formula that would produce cherries. (The formula I had originally won't match this, that too was just an example I threw together).

 

Screen Shot 2022-04-27 at 9.08.53 am.pngScreen Shot 2022-04-27 at 9.09.09 am.png

@cheeseontoast101 

 

I've created a spreadsheet to test a few assumptions, ask a question or two of you along the lines of "What-if?" that spreadsheets are supposed to help answer.

For example, take a look at this screen grab.

mathetes_0-1651069847468.png

So, some questions:

  1. The highest individual value is 50, in the third column, but the highest producing column is the second. Which is it that you'd want highlighted? 
  2. And what would you want if there were more than two individual cells with an equal "highest" value?
  3. Or two columns that yielded the same highest total?

@cheeseontoast101 

 

I've gone ahead and answered my question in the attached spreadsheet. It assumes that, with multiple rows per type of product, your "highest" would be the one with the highest total for all rows, rather than the one with highest individual cell. Here's what it looks like, and the formula (looking for the highest in the totals row) is shown there in the top. It uses the relatively new FILTER function (so you'll need the most current version of Excel).

mathetes_0-1651081858464.png

 

By the way, since it's possible that two or even three of the columns could end up producing exactly equal totals, the FILTER function will display all three in the cells to the right of the primary display. Try it when you've downloaded the spreadsheet.

 

If you're on the Beta channel, use VSTACK to consolidate the data across sheets and then use the formula of your choice to pull the column header.