We may need to lookup the next largest value in Excel, as we work on a dataset. Excel has functions that can help us to solve this problem. In this article, I will write a tutorial on finding the next largest value in a sorted or unsorted dataset.

**Table of Contents**Expand

## How to Lookup Next Largest Value in Excel: 4 Easy Ways

I will show 4 methods to find the next largest values. In the dataset, I have the *Rep Name, Rep ID, Region, Sales of 2019,* and *Sales of 2020* as attributes of a company. I have to find the next largest sales value from *Sales of 2020* according to the value selected in *Sales of 2019*. The values of this dataset are in unsorted form. I used the *Microsoft Office 365* version in this tutorial.

### 1. Using SMALL Function to Lookup the Next Largest Value After Comparing Two Columns

If the dataset is unsorted, you can use the **SMALL** and **IF** functions to find the next largest value.

**Steps**:

- Select cell
**B17**and type the following formula.

`=SMALL(IF($F$5:$F$14>E5,$F$5:$F$14),1)`

- Press
**ENTER**.

**Formula Breakdown**

**IF($F$5:$F$14>E5,$F$5:$F$14)**→ The**IF**function will check if there are numbers greater than the lookup value.**$F$5:$F$14>E5**→ is the logical test where the**IF**function will check for the next largest value.**$F$5:$F$14**→ is the lookup array where the value will be searched.**E5**→ is the cell, which contains the value of whose the next largest value will be searched.

**$F$5:$F$14**→ is the condition using which the values greater than the lookup value will be stored and if the condition does not satisfy,*FALSE*will be stored.**Output**→ array of numbers that satisfies the condition and*FALSE*text.

**SMALL(IF($F$5:$F$14>E5,$F$5:$F$14),1)**→ becomes**SMALL(array,1)**→ will return the next largest value using the position of the value on the array that the**IF**function has created.**array**is where the values satisfying the**IF**function are stored.**1**is the position of the value to return.**Output**→ $1080.00

### 2. Applying MINIFS Function to Get Next Highest Value Between Two Columns

The **MINIFS **function is an Excel function that is a combination of **MIN** and **IFS** functions. You can also use the **MINIFS **function to solve this problem.

**Steps**:

- Select cell
**B17**and type the following formula.

`=MINIFS(F5:F14,F5:F14,">"&E5)`

- Press
**ENTER**.

**Formula Breakdown**

**MINIFS(F5:F14, F5:F14,”>”&E5)**→ The**MINIFS**function will find out the next largest value according to the minimum range, criteria range, and criteria.**F5:F14**→ is the minimum range where the**MINIFS**function will search for value.**F5:F14**→ is the criteria range.**“>”&E5**→ is the criteria for finding the larger values from the provided value.**Output**→ $1080.00

**Read More: **How to Find Second Largest Value with Criteria In Excel

### 3. Inserting INDEX and MATCH Functions with Sort Feature

If the same dataset is sorted in ascending order, you can use the **INDEX** and **MATCH** functions to find the next largest value.

**Steps**:

- Select cell
**B17**and type the following formula.

`=INDEX(F5:F14,MATCH(E5,F5:F14, 1)+1)`

- Press
**ENTER**.

**Formula Breakdown**

**MATCH(E5, F5:F14, 1)+1**→ The**MATCH**function will search for the cell location from the dataset.**E5**is the searched value.**F5:F14**is the array, where the value is searched.**1**means the**MATCH**function will search for values less than the lookup value.**+1**will go for the next position the**MATCH**function has found.**Output**→ 4

**INDEX(F5:F14, MATCH(E5, F5:F14, 1)+1)**→ becomes**INDEX(F5:F14,4)**→ The**INDEX**function will search for the cell value according to the cell position found by the**MATCH**function.**F5:F14**→ is the array where the**INDEX**function will search for the value.**4**is the row number where the value is.**Output**→ $912.00

### 4. Using XLOOKUP Function to Find Corresponding Value

The **XLOOKUP** function works a little differently from the other function I have mentioned. It will search for the next largest value when there is no exact match found and return its corresponding value. I have a dataset where the attributes are *Rep Name, Rep ID, Region, Units,* and *Sales*. When I enter a non-exact *Units *value, the **XLOOKUP **function will return the *Sales *value of the next largest value.

**Steps**:

- Select cell
**B17**and type the following formula.

`=XLOOKUP(50,$E$5:$E$14,$F$5:$F$14,,1)`

- Press
**ENTER**.

**Formula Breakdown**

**XLOOKUP(50,$E$5:$E$14,$F$5:$F$14,,1)**→ The**XLOOKUP**function will search for an exact match or greater value and return a corresponding value.- 50 is the searched value.
**$E$5:$E$14**is the array, where the**XLOOKUP**function will search for the value.**$F$5:$F$14**is the return array, from where the corresponding value will be returned.- 1 means the function will search for an exact match or larger value.
**Output**→ $672.00

## Practice Section

On the right side of each worksheet, you will find a practice section to practice on your own.

**Download Practice Workbook**

You can download the practice workbook here.

## Conclusion

In this tutorial, you can learn how to lookup the next largest value in Excel both for an unsorted and sorted dataset. I have also mentioned a way to find the corresponding value. Follow our website for regular blogs related to Excel. You can suggest your thoughts about this article in the comment section below.

## Related Articles

- How to Use Excel Large Function with Criteria
- How to Use Excel Large Function with Text

- How to Use LARGE Function with VLOOKUP Function in Excel
- How to Use Excel LARGE Function with Duplicates in Excel
- How to Use Excel LARGE Function in Multiple Ranges
- How to Use LARGE and SMALL Function in Excel
- How to Use VBA Large Function in Excel
- How to Find Largest Number in Excel

**<< Go Back to Excel LARGE Function | Excel Functions | Learn Excel**