DEV Community

loading...

Excel Formulas to Lookup Next Largest Match with MATCH Function!!

excelgeek profile image Andrews Originally published at geekexcel.com on ・3 min read

So far, we have learned many formulas based on the INDEX-MATCH function. Now, we are going to see the formula only using the MATCH Function. Let’s come into this article. Here, is the tutorial to lookup the next largest match in Excel by using the MATCH function. Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel

Find Next largest match
Find Next largest match

Generic Formula:

  • Use the below formula to find the next largest value in Excel.

=MATCH(value,array,-1)

Syntax Explanations:

  • MATCH – In Excel, the MATCH function will help to locate the position of a lookup value in a row, column, or table.
  • Value – It is the given criteria.
  • Array – It represents the input ranges.
  • Comma symbol (,) – It is a separator that helps to separate a list of values.
  • Parenthesis () – The main purpose of this symbol is to group the elements.
  • Minus Operator (-) – This symbol will help to subtract any two values.

Practical Example:

Refer to the below example image. Here we will show how to get the nearest match in Excel.

  • First, we will give the input values in Column B and Column C.

Input Ranges
Input Ranges

  • Now, we are going to look up the next largest position of the given criteria.
  • Enter the above-given formula to the Cell F3.

Enter a formula
Enter a formula

  • Press the ENTER key to get the result.

Result 1
Result 1

  • Next, we are going to find the Grade for the given criteria. To find this, we will use the below formula.

=INDEX(data,MATCH(value,array,-1))

  • Apply the above formula to the formula bar section and Press the ENTER key.

Find the Grade
Find the Grade

  • Finally, we will get the result as per the given image.

Result 2
Result 2

Wind-Up:

From this tutorial, you can get to know the simple formula used to find the next largest match in Excel. Hope you like it. Let me know your doubts from the below comment box. I reply to queries frequently. To learn more, check out Geek Excel and Excel Formulas !!

Read Also:

Discussion (0)

pic
Editor guide