Google-sheets – Retrieve row data based on indirect Max function value

google sheetsgoogle-sheets-query

This spreadsheet tracks live song ratings for a popular band. Users input their score in their own column and those scores are averaged into a community score (Column E). Those song averages are averaged together to get a "show average" that is also in Column E but will never be close to the top 10 song averages so it should mess up this feature.

I am trying to have an automatic updating list that retrieves the top 10 rated songs (from column E) and lists them in descending order (highest rated at top). I can use the MAX value to get these ascending scores but am stuck in how I retrieve the other data (Columns A,B,C,D) that are the actual info corresponding to that song.

Here is a picture that should help:
enter image description here

Best Answer

Maybe something like:

=ARRAY_CONSTRAIN(query(A:E,"select * order by E desc"),10,5)

10 for the number of rows and 5 for all the columns.

Related Solutions

Google-sheets – Min/Max of a row only if value in other row is X

I believe this is what you're looking for:

=ARRAYFORMULA(min(if(B2:B6=5,A2:A6)))

The full documentation for arrayformula is at Google Support. "This function allows the specification of a range (or multiple ranges of the same size) in functions that don't normally accept ranges."

Google Sheets – Get Row Number of MAX(Range) Value

Use the following formula to do that.

Formula

=MATCH(MAX(E2:E4), E2:E4, 0)

If you want to include the header, then you have to use this formula:

=MATCH(MAX(E2:E4), E1:E4, 0)

Screenshot

enter image description here

Remark

The last criterium in the MATCH formula, 0 will trigger the function to find the first entry. Use 1 to show the last entry.

Example

I've created an example file for you: Get Row-Number of a MAX(Range) Value?