How to Lookup Next Largest Value in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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.

Overview of Excel Lookup Next Largest Value


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.

Dataset for Excel Lookup Next Largest Value


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.

SMALL Function to Lookup Next Largest Value

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.

MINIFS Function to Get Next Largest Value

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.

INDEX and MATCH Functions to Lookup Next Largest Value

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.

XLOOKUP Function to Find Corresponding Value

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.

Practice Section for Excel Lookup Next Largest Value


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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rabeya Islam
Rabeya Islam

Rabeya Islam, BSc, Computer Science and Engineering, East West University, Bangladesh, has been working with the SOFTEKO for more than one and half years. She has written some articles for ExcelDemy. Currently, she is working as the team leader, oversees the day-to-day work, and leads the SQA team Excel Extensions project. She has built the workflow and the structure of the extension testing for the team. Her work and learning interests vary from Microsoft Office Suites, VBA, and... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo