2

I have a Google Sheet that collects data and analyses any trends. What I'm trying to do is figure out how to output the text value associated with the numerical value.

Example:

Apples      7
Oranges     2
Pears       0
Mangoes     4

I grab the highest values by doing the following..

= LARGE(B:B, 1)
= LARGE(B:B, 2)
= LARGE(B:B, 3)

My desired output would look like..

Top 3
-------
Apples 7
Mangoes 4
Oranges 2

Which then changes dynamically according to the data values in column B.

But how do I then output the text value associated with this?

1

With the following formula you can accomplish that.

Formula

=QUERY(DATA!A1:B4, "SELECT A, B ORDER BY B DESC LIMIT 3")

Explained

The QUERY formula selects the two columns and orders them descendingly according to column B. Then it limits the result to three rows (top 3).

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: Top 3 results

0

Question shows OP is not expecting ties but since these are often a possibility a slightly 'safer' query might be:

=QUERY(A1:B7, "SELECT A, B where B>="&large(B:B,3)&" ORDER BY B desc, A")

This may output more than three results.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.