Normally, we can use the INDEX function to return the value at a given position in a range or array. This article will explain how to get the value based on descending order by using INDEX and MATCH Functions in Excel. Let’s get them below!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
Generic Formula:
- To retrieve the values from a table where the lookup values are sorted in descending order, you can use the below formula.
=INDEX(range1,MATCH(criteria,range2,-1))
Syntax Explanations:
- INDEX – In Excel, this function will return the value at a given position in a range or array. Read more on the INDEX function.
- MATCH – The Excel MATCH Function helps to locate the position of a lookup value in a row, column, or table.
- 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.
- Range – It represents the input data given in the worksheet.
- Minus Operator (-) – This symbol will help to subtract any two values.
Practical Example:
Now we are going to see how to get the value based on descending order in Excel.
- First, we will give the input values in Column B and Column C.
- Here, we need to find the height of the specified age that is given in Cell F2.
- We will enter the given formula in the formula bar section or cell F3.
- This formula uses -1 for match type to allow an approximate match on values sorted in descending order.
- After applying the formula, we will get the result in Cell F3.
Verdict:
From this tutorial, you can get to know the simple steps to find the value based on descending order in Excel using the INDEX and MATCH functions. Hope you like this article. Mention your queries in the comment box below. Thank you. Keep learning on Geek Excel!! *and Excel Formulas *!!
Top comments (0)