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**hide

## Download Practice Workbook

You can download the practice workbook here.

## 4 Easy Methods to Lookup Next Largest Value in Excel

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 Excel 365** version in this tutorial.

### 1. Using SMALL Function to Lookup 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**.

**Read More: ****How to Use LARGE and SMALL Function in Excel**

### 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**.

**Read More: ****How to Find Largest Lookup Value in Excel (3 Easy Ways)**

### 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**. So, When I will 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.**

**Read More: ****How to Use Excel Large Function in Multiple Ranges**

## Practice Section

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

## 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 **ExcelDemy** page for regular blogs related to Excel. You can suggest your thoughts about this article in the comment section below.