Excel Error Codes

How to Fix #N/A Error in Excel MATCH with Approximate Match

Steve K Oct 21, 2026 868 views

Question details

The user is encountering an #N/A error when using the MATCH function to find approximate values in an Excel dataset.

How to Fix #N/A Error in Excel MATCH with Approximate Match

Product: Excel
Device & OS: not provided
Scenario: Using the MATCH function with approximate match types (1 or -1) to search for a value's relative position within a lookup array.
Observed behavior: The formula returns an #N/A error instead of the expected position index, typically because the lookup array is unsorted or sorted incorrectly for the chosen match type.

Before you start

Check your MATCH formula to determine whether the match_type argument is set to 1 (less than) or -1 (greater than), as this will dictate how your lookup array must be organized.

Solution 1Recommended

Sort the Lookup Range Based on Match Type

The MATCH function requires the lookup array to be sorted in a specific order when using approximate match types, otherwise it cannot reliably locate the value.

When the match_type argument is 1, MATCH assumes the data is sorted in ascending order and searches for the largest value that is less than or equal to the lookup value. Conversely, when the match_type is -1, it assumes the data is sorted in descending order and searches for the smallest value that is greater than or equal to the lookup value.

Select the dataset

Highlight the entire dataset or the specific lookup array that your MATCH formula is referencing.

Open the Sort dialog

Navigate to the Data tab on the Excel ribbon and click on the 'Sort' button.

Apply ascending sort for match type 1

If your formula ends with '1' (e.g., =MATCH(A1, B1:B10, 1)), set the sort order to 'Smallest to Largest' (or A to Z).

Apply descending sort for match type -1

If your formula ends with '-1' (e.g., =MATCH(A1, B1:B10, -1)), set the sort order to 'Largest to Smallest' (or Z to A).

Sort the Lookup Range Based on Match Type

Error Resolved: Once the data is sorted appropriately, the MATCH function will correctly identify the position of the approximate value and the #N/A error will disappear.

Solution 2

Use XLOOKUP as an Alternative for Unsorted Data

If you cannot alter the sort order of your dataset, XLOOKUP provides a robust alternative that handles approximate matches without requiring sorted arrays.

WPS Spreadsheet Solution

Easily Manage Formulas and Fix Errors in WPS Spreadsheet

WPS Spreadsheet provides robust formula auditing tools and full support for advanced lookup functions, making it easy to identify and fix #N/A errors in your MATCH formulas.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing the #N/A errors.
2. Evaluate the formula: Click on the cell with the #N/A error, go to the 'Formulas' tab, and use the 'Evaluate Formula' tool to see exactly how the MATCH function is processing your data.
3. Sort the data: Highlight your lookup array, navigate to the 'Data' tab, and use the 'Sort' feature to arrange it in ascending or descending order based on your match type.
4. Apply alternative functions: If you prefer not to sort, replace MATCH with XLOOKUP using the formula autocomplete feature in WPS Spreadsheet.

Fully compatible with Microsoft Excel formulas (.xlsx files).Built-in error checking and formula evaluation tools.Support for modern lookup functions like XLOOKUP.Free and lightweight alternative to heavy spreadsheet applications.

microsoft office alternative - wps office

Frequently Asked Questions

Why does exact match (0) not return an #N/A error on unsorted data?

When the match_type argument is set to exactly 0, the MATCH function performs a linear search for the exact value through the entire array without assuming any specific order. Therefore, it works perfectly regardless of how the data is sorted.

Can I use VLOOKUP instead of MATCH for approximate matches?

Yes, VLOOKUP can perform approximate matches if the last argument is TRUE (or 1). However, just like the MATCH function with type 1, the lookup column must be sorted in ascending order, otherwise VLOOKUP will also return an #N/A error or incorrect results.

What if the lookup value doesn't exist even in an approximate match?

If you use match type 1 and your lookup value is smaller than the smallest value in the lookup array, it will still return an #N/A error. Similarly, for match type -1, if the lookup value is larger than the largest value in the array, it will result in #N/A.

Microsoft Office Error Codes