Function Problems

How to Use Excel INDEX and MATCH for an Approximate Month Match

Maira Mehtab Sep 21, 2026 868 views

Question details

The user needs to perform a lookup in Excel that successfully returns a result even when the exact requested number of months (such as 5 or 17) is missing from the table headers.

Product: Excel
Device & OS: not provided
Scenario: Looking up a value across a table where the column headers represent month intervals, but the specific lookup month falls between these defined intervals.
Observed behavior: A standard exact-match formula returns an error because the specific month is not present in the headers. The formula needs to be modified to perform an approximate match.

Before you start

Ensure that your lookup range (the table headers) is sorted in strictly ascending order, as an approximate match function requires ascending data to return accurate results.

Solution 1Recommended

Modify the MATCH Function for Approximate Lookup

Remove the exact match argument from the MATCH function to allow Excel to find the largest value that is less than or equal to your lookup value.

By default, adding a 0 as the third argument in a MATCH formula forces an exact match. By omitting this argument (or using 1), Excel performs an approximate match, which is ideal for checking values that fall into numerical tiers like month intervals.

Locate the formula

Select the cell containing your current INDEX/MATCH formula.

Identify the MATCH segment

Look for the MATCH portion of the formula in the formula bar, which likely looks like MATCH(TEXT(C3,"0"),$E$6:$I$6,0).

Remove the exact match parameter

Delete the ',0' at the end of the MATCH function so that it reads MATCH(TEXT(C3,"0"),$E$6:$I$6). This sets it to an approximate match.

Apply the changes

Press Enter to update the formula. The INDEX function will now pull the correct column data based on the closest approximate month.

Ascending Order Required: For the approximate match to return correct results without errors, the header range (e.g., $E$6:$I$6) must be sorted from lowest to highest.

Simplify Data Analysis

Master Advanced Formulas Seamlessly with WPS Spreadsheet

WPS Spreadsheet fully supports complex lookup functions like INDEX and MATCH, making it incredibly easy to perform exact and approximate data retrievals. It is a powerful, lightweight tool designed for efficient data management.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your lookup tables.
2. Select the target cell: Click the cell where you want the lookup result to be displayed.
3. Enter the formula: Type your =INDEX(..., MATCH(...)) formula, ensuring the third argument of MATCH is left blank for an approximate match.
4. Execute: Press Enter to seamlessly retrieve your approximate month match data.

Fully compatible with Microsoft Excel formulas, .xls, and .xlsx file formatsRobust support for INDEX, MATCH, VLOOKUP, and other advanced lookup functionsLightweight architecture ensures fast calculations for large datasetsIntuitive, user-friendly interface that requires zero learning curve for Excel users

microsoft office alternative - wps office

Frequently Asked Questions

Why is my approximate MATCH formula returning an #N/A error or the wrong value?

This usually occurs if your lookup array (the table headers) is not sorted in ascending order. When omitting the third argument (or setting it to 1), the MATCH function expects ascending data. If the data is unsorted, the formula behaves unpredictably.

Can I use an approximate MATCH if my header values are sorted in descending order?

Yes, but you must explicitly use -1 as the third argument in your MATCH formula (e.g., MATCH(lookup_value, lookup_array, -1)). This tells the function to find the smallest value that is greater than or equal to the lookup value in a descending list.

What is the purpose of the TEXT(C3,"0") function inside the MATCH formula?

The TEXT function is used here to format the numerical value in cell C3 as a text string with no decimal places. This is often necessary if the month numbers in the lookup headers are stored as text instead of true numbers, ensuring the data types match for the lookup to work.

Excel & Data Problems