How to use Max, Vlookup, and Match together-CodePudding

Is it possible to use max, Vlookup, and match together? As of now my formula is using max and Vlookup together, however I get an error each time I try to add match to it.

I have a different sheet for each month of the year. In a new sheet that reviews all of the months I'm trying to search for a specific phrase ($D94) throughout all of the sheets (C56:AY70), and find the highest value in column 49 (AY). I've accomplished this and the formula works, however now I want the highest value to match the text one cell to the right (column 50,AZ ).

=MAX
(IFERROR(VLOOKUP($D94,JANUARY!C56:AY70,49, FALSE),"0"),IFERROR(VLOOKUP($D94,FEBRUARY!C56:AY70,49, FALSE),"0"),IFERROR(VLOOKUP($D94,MARCH!C56:AY70,49)))

CodePudding user response：

you can try this in sheets:

=QUERY({{January!C56:AZ70};
{February!C56:AZ70};
{March!C56:AZ70}},
"Select Col50 Where Col1='"&D94&"' ORDER BY Col49 DESC limit 1")

CodePudding user response：

In Google Sheets, to get the max value in column AY on each sheet, along with the value in the adjacent cell, together with the sheet name, use this pattern:

=byrow( 
  { "JANUARY"; "FEBRUARY"; "MARCH" }, 
  lambda( 
    sheetName, 
    { 
      sheetName, 
      query( 
        indirect(sheetName & "!C56:AZ70"), 
        "select AY, AZ 
         where C = '" & D94 & "' 
         order by AY desc limit 1", 
        0 
      ) 
    } 
  ) 
)

In the event cell D94 or column C does not always contain a text string, use filter(), like this:

=byrow( 
  { "JANUARY"; "FEBRUARY"; "MARCH" }, 
  lambda( 
    sheetName, 
    { 
      sheetName, 
      query( 
        filter( indirect(sheetName & "!AY56:AZ70"), D94 = indirect(sheetName & "!C56:C70") ), 
        "order by Col2 desc limit 1", 0 
      ) 
    } 
  ) 
)

To get the highest value in the result table, use sortn().