Formula Errors

How to Match Multiple Excel Columns and Return a Value

Maira Mehtab Sep 22, 2026 869 views

Question details

The user needs to match values across seven columns (A through G) on one sheet with corresponding columns on another sheet, and return a value from column L while resolving a #SPILL! error caused by existing formula attempts.

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Looking up a specific value based on multiple criteria across different columns on separate worksheets.
Observed behavior: Existing formulas are producing a #SPILL! error, meaning the formula is trying to return multiple results into cells that already contain data or the array is not properly constrained.

Before you start

Ensure that the data ranges on your destination sheet and source sheet match exactly in size, and verify that there is enough empty space below the output cell to prevent array spill errors.

Solution 1Recommended

Use the FILTER Function with Boolean Logic

The most efficient way to match multiple criteria across several columns and return a single value is by using the FILTER function combined with multiplication (*) for AND logic.

When you multiply conditions in an array formula, the spreadsheet evaluates them as an AND statement. Only rows where all conditions evaluate to TRUE (represented as 1) will be returned by the FILTER function.

Select the output cell

Click on cell H2 on Sheet1 where you want the result from column L to appear.

Enter the FILTER formula

Type the following formula: =FILTER(Sheet2!$L$2:$L$17, (Sheet1!A2=Sheet2!$A$2:$A$17)*(Sheet1!B2=Sheet2!$B$2:$B$17)*(Sheet1!C2=Sheet2!$C$2:$C$17)*(Sheet1!D2=Sheet2!$D$2:$D$17)*(Sheet1!E2=Sheet2!$E$2:$E$17)*(Sheet1!F2=Sheet2!$F$2:$F$17)*(Sheet1!G2=Sheet2!$G$2:$G$17), "None found")

Apply the formula

Press Enter. The formula will search Sheet2 for a row where all columns (A through G) match Sheet1, and return the corresponding value from column L.

Understanding #SPILL! Errors: This formula works flawlessly when exactly one row matches all criteria. If multiple rows match, FILTER will attempt to output a vertical array. Ensure the output cell has enough empty space below it to accommodate multiple results, otherwise you will receive a #SPILL! error.

Advanced Formulas in WPS Spreadsheet

Master Complex Array Formulas with WPS Office

WPS Spreadsheet fully supports advanced dynamic array functions like FILTER, making it simple to resolve #SPILL! errors and match complex datasets across multiple sheets with ease.

1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple sheets.
2. Navigate to the target cell: Click on the cell where you want to retrieve the matched value.
3. Enter the dynamic formula: Input the FILTER formula and let WPS's dynamic array engine automatically handle the array evaluation.

100% compatibility with Microsoft Excel formulas like FILTER and XLOOKUPBuilt-in dynamic array engine for modern data analysisLightweight application with high performance on large datasetsFree alternative with a familiar, easy-to-use interface

Frequently Asked Questions

Why am I getting a #SPILL! error with my lookup formula?

A #SPILL! error occurs when a dynamic array formula returns multiple values, but the neighboring cells where it needs to display those results are blocked by existing data. Clearing the adjacent cells usually resolves this issue.

Can I use INDEX/MATCH instead of FILTER for multiple columns?

Yes, you can use an array version of INDEX and MATCH. For example: =INDEX(Sheet2!$L$2:$L$17, MATCH(1, (Sheet1!A2=Sheet2!$A$2:$A$17)*(Sheet1!B2=Sheet2!$B$2:$B$17), 0)). In older versions of spreadsheet software, this requires pressing Ctrl+Shift+Enter.

What does the asterisk (*) do in the FILTER formula conditions?

The asterisk acts as a mathematical AND operator for arrays. It multiplies the TRUE (1) and FALSE (0) results of each condition. A row is only evaluated as TRUE (and therefore returned by FILTER) if all multiplied conditions equal 1.

Excel & Data Problems