How to Fix Excel SMALL Function Returning 0 Instead of First Match
Question details
The user needs to correct an array or lookup formula where the SMALL function is incorrectly returning 0 instead of the first matching row.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Using the SMALL function in combination with ROW and IF logic to extract the first matching value or row number from a dataset.
- Observed behavior
- The formula returns 0 because the underlying conditional logic assigns zeros to non-matching rows, which the SMALL function interprets as the smallest numeric value.
Select the cell containing your formula and identify the nested IF statement that evaluates your criteria before making adjustments.
Adjust IF Logic to Return FALSE or Errors for Non-Matches
Modify the underlying IF formula so that rows failing the condition evaluate to FALSE or an error like #DIV/0! instead of the number 0.
When using an array formula to find the smallest matching row, returning 0 for non-matching conditions forces the SMALL function to evaluate those zeros. Because 0 is typically smaller than your valid row numbers, SMALL will output 0. Changing the false condition to FALSE or an error bypasses this issue.
Click into the Formula Bar and find the IF statement nested inside your SMALL function (for example, IF(A1:A10="Match", ROW(A1:A10), 0)).
Delete the '0' from the value_if_false argument. You can simply leave it blank by closing the parenthesis early (e.g., IF(A1:A10="Match", ROW(A1:A10))), which automatically returns FALSE.
Alternatively, you can replace the 0 with 1/0 to intentionally generate a #DIV/0! error for non-matching rows.
Depending on your Excel version, press Ctrl + Shift + Enter to execute the array formula correctly.
Troubleshoot Array Formulas Easily in WPS Spreadsheet
WPS Spreadsheet provides a robust and highly compatible environment for building, evaluating, and troubleshooting complex array formulas like SMALL, IF, and ROW.
- 1. Open your workbook: Launch WPS Spreadsheet and open the .xlsx file containing your problematic SMALL formula.
- 2. Evaluate the formula: Navigate to the 'Formulas' tab on the top ribbon and click on 'Evaluate Formula'.
- 3. Step through the logic: Click 'Evaluate' repeatedly to see exactly which part of your IF statement is generating the unwanted zeros.
- 4. Apply the fix: Edit the formula in the formula bar to remove the 0 for non-matches, and press Enter (or Ctrl+Shift+Enter) to save.
Frequently Asked Questions
Why does the SMALL function count zero as a valid match?
In spreadsheet software, 0 is a standard numeric value. Since the SMALL function evaluates the dataset to find the k-th smallest number, it treats 0 as a valid entry, prioritizing it over larger positive row numbers.
How do I ignore errors in a SMALL array formula?
If you configure your formula to return #DIV/0! for non-matches, you can use the AGGREGATE function instead of SMALL. Using AGGREGATE(15, 6, array, k) applies function 15 (SMALL) and uses option 6 to explicitly ignore all errors.
Do I need to press Ctrl+Shift+Enter for SMALL functions?
If you are nesting an IF statement inside a SMALL function to check a range of cells against a criteria (e.g., SMALL(IF(...))), it operates as an array formula. In older versions of Excel, you must press Ctrl+Shift+Enter for the array to process correctly, though modern dynamic array engines handle this automatically.
