Google Sheets LARGE function is better than its MAX function. Both these functions are related to finding the Maximum value in a data range. You can say LARGE is just opposite to SMALL function.

Purpose of Large Function in Google Sheets:

When you want to find the nth largest value from a range or array, you can use the LARGE function.

LARGE Function Syntax:

LARGE(DATA, N)

Below examples can shed some light on the use of large formula in Google Doc Spreadsheets.

How to Use Large Function in Google Sheets

Examples of the use of LARGE formula in Google Sheets.

Example 1:

In cell D2 the formula returns the first largest number in the range B2:B5. In D3 and D4 the formula returns the second and first largest values in the range respectively.

The formula in D4 is standout from the rest because of the use of ‘Data’ as comma separated cell reference in it. I will explain that after 2 paras below.

What about cell D5? In D5, the formula range is A2:A5 and it returns #NUM! error. This’s because the selected range in the formula is only containing text values.

But when you come down to cell D6, there you can see the range as A2:B5. This range contains both numeric as well as text values. Here large function returns the largest element correctly.

One more thing. In D4, you can see a different use of the Large function. When you want to find the nth largest number of non-consecutive cells, you can adopt the method used in that cell. Here is another example.

=large({100,200,300,400},2)

This formula would return the number 300 as it’s the second largest number. Don’t forget to use Curly Brackets to separate the elements with the user-defined nth number.

Example 2

Now the use of Google Sheets Large function with Filter.

When using LARGE with Filter function as above, the formula not only returns the top value in the range but also it returns the other values in the row.

Google Sheets LARGE Function and How It’s Different From MAX

1. Max function can return only the first largest element in the range. But in Large, you can specify the position of the value like first highest, second highest and so on.

2. When you use the Large function in a range that contains text values only, it returns “#NUM!” error. But you can use MAX function regardless of the values in the range. If the range in Max contains text values only, it would return 0.

Find N Number of Largest Values (Not Nth)

As I detailed above in example 2, you can use the LARGE function with the Filter in a range to return nth largest value or element. But what about returning largest N number of values. I’m not talking about nth.

In this case, I suggest you use Query function. See the above example 2 where I’ve filtered the row containing the largest value in C2:C5, that means the maximum qty. sold item.

=query(A1:D5,"Select * order by C desc limit 2")

This Query formula simply returns two rows containing the first largest sold item and the second largest sold item. That’s all for now. Enjoy!