Function Problems

Excel Formula to Return a Value When Multiple Conditions Match

Algirdas Jasaitis Oct 16, 2026 869 views

Question details

The user needs a formula to retrieve a document number from another worksheet when multiple criteria, such as customer name and various amount columns (e.g., Days 60, Days 90, Days 120), match the current row.

How to Return a Value When Multiple Conditions Match in a Spreadsheet

Product: Spreadsheet
Device & OS: not provided
Scenario: Looking up and returning specific data points across different worksheets based on complex, multi-column criteria.
Observed behavior: Standard single-condition lookup functions like VLOOKUP are insufficient. A more advanced formula is required to evaluate multiple arrays simultaneously and output the exact matching record.

Before you start

Verify that your data ranges across both sheets are of the exact same size, and check if your version of the spreadsheet application supports modern array functions like XLOOKUP.

Solution 1Recommended

Use the INDEX and MATCH Array Formula

This combination is highly compatible across nearly all versions of Excel and WPS Spreadsheet, allowing you to match multiple conditions using Boolean logic.

By multiplying arrays of logical tests together (e.g., Array1=Condition1 * Array2=Condition2), this formula forces the MATCH function to find the row where all conditions evaluate to TRUE (represented by the number 1).

Select the target cell

Click on the cell where you want the document number to appear (for example, C2).

Enter the formula

Type the following formula: =IFERROR(INDEX(Sheet2!$C$2:$C$1000,MATCH(1,(Sheet2!$A$2:$A$1000=B2)*(Sheet2!$D$2:$D$1000=D2)*(Sheet2!$E$2:$E$1000=E2)*(Sheet2!$F$2:$F$1000=F2),0)),""). Adjust the cell references according to your actual worksheet data.

Apply as an Array Formula

If you are using an older spreadsheet version, press Ctrl+Shift+Enter to execute the formula. Newer versions that support dynamic arrays simply require you to press Enter.

Fill down the formula

Click and drag the fill handle at the bottom-right corner of cell C2 to copy the formula down to the rest of the column.

Absolute References: Using absolute references (the $ signs) in your formula is crucial to ensure that the lookup ranges do not shift when you drag the formula down the column.

Solution 2

Use the XLOOKUP Function with Concatenation

For users with newer versions of spreadsheet software, XLOOKUP provides a simpler syntax by concatenating the lookup values and lookup arrays.

Advanced Spreadsheet Formulas

Easily Handle Complex Data Lookups with WPS Spreadsheet

WPS Spreadsheet fully supports advanced data handling formulas like XLOOKUP, INDEX, and MATCH natively. You can effortlessly execute multi-condition lookups to manage large datasets without compatibility issues.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the workbook containing your customer and document data.
2. Select the cell: Click on the cell where you want the retrieved value to be displayed.
3. Type the formula: Enter your preferred multi-condition lookup formula, such as =XLOOKUP or =INDEX and MATCH.
4. Execute and Fill: Press Enter to retrieve the data, then use the fill handle to drag the formula across the required rows.

Fully compatible with Microsoft Excel formulas and .xlsx formatSupports modern dynamic arrays and functions like XLOOKUPLightweight, fast execution on complex datasetsFree to use with a familiar interface

microsoft office alternative - wps office

Frequently Asked Questions

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

This error frequently occurs in older spreadsheet versions if you press Enter instead of Ctrl+Shift+Enter when confirming an array formula. It can also happen if the ranges provided in your arrays are not of equal length.

Can I use VLOOKUP to match multiple conditions?

VLOOKUP natively evaluates only one lookup condition. To use it for multiple conditions, you must create a new 'helper' column in both your source data and search area that concatenates the criteria (e.g., =A2&B2) and use that combined column for your VLOOKUP.

Is the XLOOKUP function available in WPS Spreadsheet?

Yes, newer versions of WPS Spreadsheet fully support the XLOOKUP function, allowing you to seamlessly use it for multi-condition lookups, reverse lookups, and custom error handling without needing complex combinations.

Excel & Data Problems