Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • How to Use Solver Tool in Excel
  • How to create dynamic reference table name in Excel
  • Get column name from index in Excel Table
  • How To Compare Two Lists in Excel
  • Subtotal function in Excel

References

  • How to get last column number in range in Excel
  • How to get address of first cell in range in Excel
  • Get nth match with INDEX / MATCH in Excel
  • How to use Excel FORMULATEXT function
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation only dates between
  • Prevent invalid data entering in specific cells
  • Data validation must not exist in list
  • Excel Data validation unique values only

INDEX and MATCH descending order in Excel

by

This tutorial shows how to calculate INDEX and MATCH descending order in Excel using the example below;

Formula

=INDEX(range1,MATCH(lookup,range2,-1))

Explanation

To retrieve values from a table where lookup values are sorted in descending order [Z-A] you can use INDEX and MATCH, with MATCH configured for approximate match using a match type of -1. In the example shown, the formula in F5 is:

=INDEX(C5:C9,MATCH(F4,B5:B9,-1))

where values in B5:B9 are sorted in descending order.

Context

Suppose you have a product that is sold in rolls of 100 feet, and orders are allowed in whole rolls only. For example, if you need 200 feet of material, you need two rolls total, and if you need 275 feet, you’ll need to buy three rolls. In this case, you want the formula to return the “next highest” tier whenever you cross over an even multiple of 100.

How this formula works

This formula uses -1 for match type to allow an approximate match on values sorted in descending order. The MATCH part of the formula looks like this:

MATCH(F4,B5:B9,-1)

Using the lookup value in cell F4, MATCH finds the first value in B5:B9 that is greater than or equal to the lookup value. If an exact match is found, MATCH returns the relative row number for that match. When no exact match is found, MATCH continues through the values in B5:B9 until a smaller value is found, then it “steps back” and returns the previous row number.

In the example shown, the lookup value is 275, so MATCH returns a row number of 3 to INDEX:

=INDEX(C5:C9,3)

The INDEX function then returns the third value in the range C5:C9, which is $383.

Post navigation

Previous Post:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • IF with boolean logic in Excel
  • AND function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel AND Function
  • IFNA function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • DATEDIF function: Description, Usage, Syntax, Examples and Explanation
  • Convert decimal hours to Excel time
  • Calculate total hours that fall between two times in Excel
  • How to get number of days, weeks, months or years between two dates in Excel
  • How to get workdays between dates in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Calculate conditional mode with criteria in Excel
  • Map text to numbers in Excel
  • How to randomly assign people to groups in Excel
  • Group times into 3 hour buckets in Excel

General

  • Convert column letter to number in Excel
  • Customize Ribbon In Excel
  • Automatically fill series of cells in Excel using AutoFill
  • Basic error trapping example in Excel
  • Common Errors in Excel
© 2026 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning