Fix #N/A Error in INDEX and MATCH Pricing Formula in Excel
Question details
The user needs to resolve an #N/A error that occurs when using an INDEX and MATCH formula to calculate pricing.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Setting up a two-way lookup for a pricing table using INDEX and MATCH with approximate matching criteria.
- Observed behavior
- The formula returns an #N/A error because the lookup range is not sorted correctly according to the chosen match type.
Ensure you know whether your pricing model requires an exact match (0), a less than match (1), or a greater than match (-1), as this determines how your lookup data must be sorted.
Sort Values in Descending Order and Use Match Type -1
Fix the #N/A error by arranging your lookup array correctly to support the -1 approximate match type.
When using the MATCH function with the match_type set to -1 (greater than), Excel requires the lookup array to be sorted in descending order (largest to smallest). If the data is arranged randomly or in ascending order, the function fails to evaluate properly and throws an #N/A error.
Select the header range containing your quantity or size values (e.g., B1:E1) and sort them in descending order.
Click on the cell where you want the pricing result to be displayed.
Type the combined INDEX and MATCH formula using -1 for the approximate match and 0 for the exact match. For example: =INDEX(B2:E6, MATCH(B9, B1:E1, -1), MATCH(B8, A2:A6, 0)).
Press the Enter key to apply the formula. The correct price will now display instead of the #N/A error.
Use WPS Spreadsheet for Flawless INDEX and MATCH Formulas
WPS Spreadsheet provides robust formula support, intelligent syntax helpers, and real-time error checking, making it incredibly easy to build complex pricing models without running into frustrating #N/A errors.
- 1. Open your data in WPS: Launch WPS Spreadsheet and open your pricing table workbook.
- 2. Sort your arrays: Navigate to the 'Data' tab and use the Sort tool to arrange your columns or rows in descending order.
- 3. Input the formula: Select your output cell and start typing =INDEX. Follow the on-screen syntax prompts to build your lookup.
- 4. Calculate the price: Hit Enter to complete the formula and seamlessly retrieve your matched pricing data.
Frequently Asked Questions
Why does my INDEX and MATCH formula keep returning an #N/A error?
The #N/A error occurs when the MATCH function cannot find the lookup value. For exact matches (0), it means the value doesn't exist in the range. For approximate matches (1 or -1), it usually means the lookup array is not sorted correctly.
Can I use VLOOKUP instead of INDEX and MATCH for my pricing table?
Yes, VLOOKUP can be used if your lookup value is strictly in the first column of the range. However, INDEX and MATCH offer much more flexibility, such as conducting two-way lookups across both rows and columns simultaneously.
How do I find the closest match if the exact pricing tier isn't listed?
You can achieve this by using an approximate match. Set the MATCH function's third argument to 1 (and sort your data ascending) to find the next smallest value, or set it to -1 (and sort descending) to find the next largest value.
