Home > Mobile >  Return multiple rows of data from INDEX, MATCH, MAXIFS - Google Sheets
Return multiple rows of data from INDEX, MATCH, MAXIFS - Google Sheets

Time:10-14

Sample Data: https://docs.google.com/spreadsheets/d/1CeksKcxsf10j_hnp7W1MoofBU-sGRp-d8oD_mrj14IY/edit?usp=sharing

Long story short, I used the INDEX, MATCH, and MAXIFS functions to return data from another sheet that matched certain criteria:

=index('Reading Journal'!A:A, match(maxifs('Reading Journal'!E:E, 'Reading Journal'!AB:AB, "January"), 'Reading Journal'!E:E, 0))

But it's only showing the first row of matching data and I'd like it to return all rows that match the criteria.

In the spreadsheet, the cell in question is C21 in the sheet 'January Stats' returning data from the 'Reading Journal' spreadsheet. I'd like for the formula to return the TITLE(s) [column A] with the highest RATING [column E] of all the data marked "January" under MONTH READ [column AB]. So if multiple entries have the same highest rating for the month, I'd like it to return all of them.

I can't tell if it might be possible with an Array or Query formula but I'm learning these on the fly and it's making my eyes cross.

CodePudding user response:

try:

=TEXTJOIN(", ", 1, FILTER('Reading Journal'!A:A, 
 'Reading Journal'!E:E=MAXIFS('Reading Journal'!E:E, 
 'Reading Journal'!AB:AB, "January"), 'Reading Journal'!AB:AB="January"))
  • Related