Forum Discussion
Find Highest Value In A Row, Return the column header - across sheets
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).
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).
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.