Fix INDEX MATCH Returning #N/A in Excel 365 for Mac
Question details
The user needs a formula that returns data based on a model number, a column header selected from a drop-down list, and the first value meeting a greater-than-or-equal condition without returning #N/A.
- Product
- Microsoft Excel 365 for Mac
- Device & OS
- macOS
- Scenario
- The user is building a lookup formula that must combine multiple exact criteria with a closest value or greater-than-or-equal lookup and return matching data without errors.
- Observed behavior
- The INDEX MATCH formula returns #N/A instead of the expected record or row.
Before editing formulas, confirm that the header row used in MATCH covers the same columns as the result range. If you plan to use XLOOKUP match mode 1 or an approximate MATCH, sort the lookup column in ascending order first.
Use FILTER to return rows that match model, selected header, and a >= condition
Use this solution in Excel 365 for Mac when you need the full row or multiple qualifying rows instead of debugging a nested INDEX MATCH array formula.
FILTER evaluates the model, selected header, and greater-than-or-equal condition as a single dynamic array formula, so it returns matching rows without requiring the legacy Ctrl+Shift+Enter array entry.
The formula uses the header selected in D6 to locate the correct column, then compares that column to the threshold in D9.
Click the cell where you want the filtered result to start. Make sure there is enough blank space below and to the right for the returned rows and columns.
Type =FILTER(A10:O255,(A10:A255=D5)*(INDEX(A10:O255,0,MATCH(D6,A10:O10,0))>=D9),"No matching row") and press Return.
Confirm that the spilled range contains only the expected rows. If a #SPILL! error appears, clear the cells that are blocking the output range.
Use XLOOKUP with match mode 1 for the first value greater than or equal to D9
Use this solution when you want one value back and the selected lookup column is sorted in ascending order.
Clean hidden spaces and text-number mismatches that cause #N/A
Use this solution when the formula structure is correct but Excel still cannot find a match.
Use WPS Office Spreadsheets as a Free Alternative for Excel 365 Lookup Workbooks
WPS Office is a free, lightweight office suite that opens Excel .xlsx workbooks, preserves formulas, and offers a familiar ribbon interface for Mac and Windows users migrating from Microsoft Excel 365.
Frequently Asked Questions
Why does my INDEX MATCH formula still show #N/A after I fix the syntax?
Usually a hidden space, trailing apostrophe, or text-number mismatch prevents Excel from matching values. Use =ISNUMBER(cell) to check numeric cells, run Data > Text to Columns to convert text numbers, and use Find & Replace to remove spaces.
Can I use FILTER instead of INDEX MATCH in Excel 365 for Mac?
Yes. Enter =FILTER(A10:O255,(A10:A255=D5)*(INDEX(A10:O255,0,MATCH(D6,A10:O10,0))>=D9),"No matching row") to return all rows that meet the model, selected header, and greater-than-or-equal condition.
Does XLOOKUP match mode 1 require sorted data?
Yes. Match mode 1 performs an exact match or next larger match, so the lookup array must be sorted in ascending order. If the data cannot be sorted, use FILTER with a >= condition instead.
What should I check when the header selected from a drop-down list is not matching any column?
Make sure the MATCH header range, for example A10:O10, includes the drop-down header's exact text, including capitalization and trailing spaces. Also confirm the range covers the same columns as the result range.
How do I return the first value that is greater than or equal to D9 without sorting?
Use FILTER to qualify the values and wrap the result in MIN: =MIN(FILTER(INDEX(A10:O255,0,MATCH(D6,A10:O10,0)),(A10:A255=D5)*(INDEX(A10:O255,0,MATCH(D6,A10:O10,0))>=D9))). This returns the smallest qualifying value without changing the sort order.
