LARGE & SMALL Functions in Excel – Find the Kth Largest and Smallest Values

Sometimes in Excel, finding just the highest or the lowest value is not enough.

You may want to know:

• What is the 2nd highest sale?
• Who scored the 3rd lowest mark?
• What is the 5th largest expense?
• Which is the top 3rd order value?

MAX and MIN can only give you:

• The biggest
• Or the smallest

But what if you want the values in between?

That’s where the LARGE and SMALL functions help.

In simple words:

LARGE = finds the Kth largest value

SMALL = finds the Kth smallest value

(K means position: 1st, 2nd, 3rd, 4th….)

What Are LARGE & SMALL Functions?

• LARGE → returns the Nth largest value
• SMALL → returns the Nth smallest value

They work with:

• positive numbers
• negative numbers
• decimals
• zeros

They ignore:

• text
• blank cells

Syntax:

LARGE:

=LARGE(array, k)

SMALL:

=SMALL(array, k)

Arguments:

• array → the range of numbers
• k → the position you want (1 = largest/smallest, 2 = second, 3 = third…)

Examples:

Order ID	Salesperson	Amount	Date
101	John	1200	01-01-2024
102	Sara	-300	03-01-2024
103	Amit	450	05-01-2024
104	Neha	2000	06-01-2024
105	Riya	-150	08-01-2024
106	Ali	800	10-01-2024
107	Priya	3000	12-01-2024
108	Karan	100	15-01-2024
109	Mehul	0	18-01-2024
110	Asha	1700	20-01-2024

LARGE Examples (Top Values):

1. Find the 2nd Largest Sale:

=LARGE(C2:C11, 2)

Result → 2000

SMALL Examples (Top Values):

1. Find the Smallest Value:

=SMALL(C2:C11, 1)

Result → -300

2. Find the 2nd Smallest Value:

=SMALL(C2:C11, 2)

Result → -150

Difference Between MIN/MAX and SMALL/LARGE:

Function	What It Finds
MAX	Only the highest value
MIN	Only the lowest value
LARGE	1st, 2nd, 3rd… highest
SMALL	1st, 2nd, 3rd… lowest

When Should You Use LARGE?

Use LARGE when you want:

• Top 3 sales
• Highest performers
• Biggest expenses
• Top exam scores
• Highest profits

When Should You Use SMALL?

Use SMALL when you want:

• Lowest expenses
• Bottom scores
• Smallest payments
• Most negative values
• Weakest performers

Related Functions (Quick Guide):

Function	Purpose
MAX	Highest value
MIN	Lowest value
LARGE	Kth largest
SMALL	Kth smallest
MAXIFS	Highest value with conditions
MINIFS	Lowest value with conditions

In Simple Words:

• MAX/MIN → only top or bottom
• LARGE/SMALL → any position (2nd, 3rd, 4th…)

LARGE and SMALL help you understand your data in more detail — not just the extremes, but everything in between.

If you want to go a step further and see how values are positioned relative to each other (1st, 2nd, 3rd, including ties), Excel’s ranking functions are the right choice.