Function Problems

How to Use Excel Formulas to Return Values Using Two Criteria Without Spill Errors

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs an Excel formula to return matching products and stores based on two specific criteria (employee name and date) without causing spill errors, while leaving cells blank when no further matches exist.

Product: Excel
Device & OS: not provided
Scenario: Extracting matching data records based on multiple conditions like employee name and date into a specific cell range.
Observed behavior: Needs to retrieve matching records across specific columns while ensuring that cells without matching data display as blank and avoid triggering #SPILL! errors.

Before you start

Ensure your dataset is organized in a clear tabular format without merged cells. Verify the exact columns where your lookup criteria and return values are located.

Solution 1Recommended

Use INDEX, SMALL, IF, and IFERROR to Extract Data

This combination creates a traditional array formula that extracts multiple matching records based on two criteria while confining the results to specific cells, thereby avoiding spill errors entirely.

By combining INDEX, SMALL, and IF, you can create a powerful formula that searches for multiple criteria and returns the corresponding row numbers sequentially.

The IFERROR function is then used to hide any errors and return a blank cell when no more matches are found, keeping your data layout clean.

Identify your criteria and data columns

Locate the cells containing your lookup criteria (e.g., employee name in G2 and date in G3). Next, identify your source data columns, such as Employee in Column B, Date in Column C, Product in Column D, and Store in Column E.

Enter the formula for the first return value

Select the first output cell (e.g., G5 for Products) and enter the combined array formula using IFERROR, INDEX, SMALL, and IF. The logic should check if Column B matches G2 and Column C matches G3, then return the corresponding row from Column D.

Confirm as an array formula

If you are using an older version of Excel that does not support dynamic arrays natively, press Ctrl + Shift + Enter after typing the formula. This evaluates it as an array formula and surrounds it with curly braces {}.

Fill the formula across and down

Copy the formula from G5 to H5, adjusting the INDEX range to reference Column E for Stores. Then, select both G5 and H5, and drag the fill handle down through row 7 to extract the remaining matches or display blank cells for empty results.

Handling Empty Matches: The IFERROR function is crucial here. Once the SMALL function runs out of matching records, it generates an error. IFERROR catches this and replaces it with an empty string (""), resulting in a blank cell.

Efficiently handle complex formulas in WPS Spreadsheet

Solve Multi-Criteria Lookups with WPS Office

WPS Spreadsheet provides robust support for advanced array formulas, including INDEX, MATCH, and dynamic arrays. You can seamlessly perform complex data extractions based on multiple conditions just like in Excel, but in a free and lightweight application.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx file containing the employee, date, product, and store data.
2. Input the array formula: Select your target cell (e.g., G5) and enter your INDEX, SMALL, and IF array formula to check multiple criteria against your columns.
3. Drag to fill results: Use the fill handle at the bottom right of the cell to drag the formula down. The IFERROR function will automatically keep empty match cells blank.

Fully compatible with Microsoft Excel formulas and .xlsx file formatsSupports advanced functions like FILTER, INDEX, MATCH, and array calculationsFree, lightweight, and fast-loading spreadsheet solutionClean and familiar user interface for seamless transition

microsoft office alternative - wps office

Frequently Asked Questions

Why am I getting a #SPILL! error when extracting data?

A #SPILL! error occurs when a modern dynamic array formula returns multiple results, but the neighboring cells where the results need to overflow (spill) are not empty. To fix this, either clear the blocking cells or use traditional array formulas like INDEX and SMALL to confine results to specific individual cells.

Can I use the FILTER function instead of INDEX and SMALL?

Yes, if you are using a newer version of Excel or WPS Spreadsheet that supports dynamic arrays, you can use the FILTER function. For example: =FILTER(D2:E100, (B2:B100=G2)*(C2:C100=G3), ""). This is simpler, but you must ensure there is enough empty space below the formula for the results to spill without triggering an error.

Why is my INDEX and SMALL array formula returning a #VALUE! error?

This often happens if you forget to confirm the formula with Ctrl + Shift + Enter in non-dynamic array versions of Excel. It can also occur if the range sizes for your criteria arrays and return arrays do not match exactly (e.g., mixing rows 2:100 with rows 2:90).

How does IFERROR help in this specific formula?

When the SMALL function finishes listing all valid matching records, it generates a #NUM! error for the remaining cells you dragged the formula into. Wrapping the entire formula in IFERROR(..., "") catches this error and displays a blank cell instead, keeping your dashboard neat and clean.

Excel & Data Problems