CODEWITHSUNDEEP
google-sheetsindexingmaxgoogle-sheets-formula
difab
difab

Reputation: 79

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

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]

Upvotes: 2

Views: 909

Answers (2)

tomf
tomf

Reputation: 535

This is a slightly longer version of @player0's. Either will return the same response. They both do a FLATTEN and then a SPLIT to generate three columns. Test each out with your data to see if either is faster. They should be roughly the same, but its worth testing.

=ARRAYFORMULA(
  IF(ISBLANK(A2:A),,
   IFERROR(
    VLOOKUP(
     A2:A,
     QUERY(
      SPLIT(
       FLATTEN(
        IF(ISBLANK('BAR SALES'!A2:A),,
         'BAR SALES'!A2:A&"|"&'BAR SALES'!B1:F1&"|"&'BAR SALES'!B2:F)),
       "|"),
      "where Col3 is not null
       order by Col3 desc"),
     2,FALSE))))

Upvotes: 0

player0
player0

Reputation: 1

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)))

enter image description here

Upvotes: 2

Related Questions