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.
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
- SMALL(array,1) → will return the next largest value using the position of the value on the array that the IF function has created.
- $F$5:$F$14>E5 → is the logical test where the IF function will check for the next largest value.
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
- INDEX(F5:F14,4) → The INDEX function will search for the cell value according to the cell position found by the MATCH function.
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
