How do I find a row, find the highest value in that row, and return that column header?-CodePudding

In the below spreadsheet, I am trying to find the bar with the highest sales per show. So I want the formula in cell B2 on the 'FRONT SHEET' to look at the 'BAR SALES' sheet and find the specific show, find the highest sales in that row, and then pull the bar name.

I am currently using this formula which works; however, the formula is specific to this show. I want the formula to be broader and be able to search for the show in the 'BAR SALES' sheet.

=INDEX('BAR SALES'!$B$1:$F$1,MATCH(MAX('BAR SALES'!B2:F2,'BAR SALES'!B2:F2),'BAR SALES'!B2:F2,0))

This is a dummy spreadsheet but has the gist. My actual sheet is a lot bigger so I want to be able to search a long list of shows for this information without specifically tailoring the formula to each show in that list. Can I add a vlookup in this formula somehow??

[https://docs.google.com/spreadsheets/d/1dcjjQyZj9ANUTyTMloiY2CX94nBSYLt5hCiSWzY3tBk/edit#gid=1376876918][1]

CodePudding user response：

You could use the exact same function you currently have and just have a script to autofill the next rows with the same function every time you add a new value in the "shows" column.

Here is the script:


function fillDownFunctionB() {
  var ss= SpreadsheetApp.getActiveSpreadsheet().getSheetByName("FRONT SHEET")
  var lastsheetrow = ss.getLastRow() //last row with data
  
  //last row in the autofill range with data
  var lastrangerow = ss.getRange("B1:B").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow()
  
  var sourcerange = ss.getRange(2, 2,lastrangerow-1)
  var destinationrange = ss.getRange(2, 2, lastsheetrow-1)
  
  sourcerange.autoFill(destinationrange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES)

}

You can just open the Apps Script code editor from the sheet extensions, then paste the code there, click save and add a trigger. To do this you can just click the alarm clock icon on the left side, then click the Add trigger button, and select the following options:

After that, if it detects any value in column A, it will attempt to automatically run the function with the data from the source sheet, so if you add new values to the column A it will run automatically with the data from the source sheet.

Sample result:

CodePudding user response：

use:

=ARRAYFORMULA(IFNA(VLOOKUP(A2:A, SORTN(SORT(SPLIT(
 FLATTEN('BAR SALES'!A2:A&"×"&'BAR SALES'!B1:F1&"×"&'BAR SALES'!B2:F), 
 "×"), 3, 0), 9^9, 2, 1, 1), 2, 0)))