Fix Excel MATCH Returning #N/A in an Array Formula
Question details
The user needs an Excel MATCH formula to return the position of the first value meeting a threshold instead of #N/A.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A MATCH formula searches a Boolean array created by comparing a range of values with a threshold.
- Observed behavior
- The worksheet formula returns #N/A even though the expected result appears in the Function Arguments dialog.
Check whether your Excel version supports dynamic arrays or requires Ctrl+Shift+Enter for legacy array formulas. Confirm the comparison range and threshold cell; the examples below use A1:A10 and B1.
Enter the MATCH formula as a legacy array formula
Use this method in older Excel versions when the formula works in the Function Arguments dialog but returns #N/A in the worksheet.
Select the cell containing the MATCH formula and click in the formula bar. For a first-value-at-or-above-threshold search, the example formula is =MATCH(TRUE,A1:A10>=B1,0).
In an older Excel version that requires legacy array entry, press Ctrl+Shift+Enter instead of Enter. Excel adds braces around the formula automatically; do not type the braces yourself.
Select the result cell and verify that it returns the position of the first qualifying value within A1:A10. In dynamic-array-enabled Excel, try confirming the same formula with Enter instead.
Use an INDEX-based MATCH formula with normal entry
Use this alternative to evaluate the comparison array without legacy Ctrl+Shift+Enter entry.
Find the First Value Meeting a Threshold in WPS Spreadsheets
Use MATCH with INDEX in WPS Spreadsheets to find the first value at or above a threshold. The INDEX-based formula lets you perform the comparison with normal formula entry.
- 1. Open the workbook: Launch WPS Spreadsheets and choose Open to load your Excel workbook, or create a blank spreadsheet.
- 2. Identify the input cells: Select your data range and threshold cell. For this example, place the values in A1:A10 and the threshold in B1.
- 3. Enter the threshold formula: Click an empty result cell, type =MATCH(TRUE,INDEX(A1:A10>=B1,0),0), and press Enter.
- 4. Check and save the result: Verify that the returned position identifies the first qualifying value. Choose Save As and select .xlsx if you need to share the workbook with Excel users.
Frequently Asked Questions
Why does MATCH return #N/A when the Function Arguments dialog shows the right answer?
In older Excel versions, the dialog may evaluate the comparison array correctly while the worksheet formula still requires array entry. Select the formula cell, edit it in the formula bar, and confirm with Ctrl+Shift+Enter.
Can I use this MATCH formula without Ctrl+Shift+Enter?
Yes. Enter =MATCH(TRUE,INDEX(A1:A10>=B1,0),0) and press Enter. Dynamic-array-enabled Excel can also evaluate =MATCH(TRUE,A1:A10>=B1,0) with normal entry.
What does the final 0 in the MATCH formula do?
The final 0 requests an exact match. In this formula, MATCH searches for the first TRUE in the comparison array, so the source values do not need to be sorted.
What if the formula still returns #N/A after correcting array entry?
Check the referenced range, the threshold, and whether at least one value satisfies the comparison. Also check that values intended to be numeric are stored as numbers. If no comparison returns TRUE, MATCH correctly returns #N/A.
Does MATCH return the worksheet row number?
No. MATCH returns the position within the search range. For example, a result of 3 for A5:A14 points to A7, the third cell in that range, rather than worksheet row 3.
