Function Problems

How to Use Excel XLOOKUP for Three Criteria Matching

Phi Hung Vo Sep 25, 2026 872 views

Question details

Find a specific value in a data table by matching three different criteria, such as two row conditions and one column condition.

How to Do a Three-Criteria Lookup in Excel

Product: Excel
Device & OS: not provided
Scenario: The user needs a scalable formula to look up values based on multiple inputs across rows and columns, and fill the formula down a column for multiple records.
Observed behavior: The formula successfully retrieves the correct table value or returns a blank if no matching combination is found.

Before you start

Verify that the data types in your lookup values match the data types in your table arrays, and ensure you are using an Excel version that supports dynamic array functions if you plan to use XLOOKUP.

Solution 1Recommended

Use a Nested XLOOKUP Formula with Concatenation

This approach combines the first two criteria and nests a second XLOOKUP to dynamically identify the correct column header.

By combining multiple criteria into a single string using the ampersand (&) operator, XLOOKUP can evaluate complex logic without requiring traditional array entry.

Select the destination cell

Click on the cell where you want the lookup result to appear (for example, cell D2).

Enter the nested formula

Type the formula =IFERROR(TOCOL(XLOOKUP(A2&B2,$A$12:$A$16&$B$12:$B$16,XLOOKUP(C2,$C$11:$G$11,$C$12:$G$16)),3),"") into the formula bar.

Apply to remaining records

Press Enter to calculate the result, then click and drag the fill handle from the bottom-right corner of the cell down to apply it to the rest of your data.

Use a Nested XLOOKUP Formula with Concatenation

Understanding the logic: The A2&B2 portion creates a unique text string to match against the combined lookup array, while the nested XLOOKUP isolates the specific column needed for the third criterion.

Solution 2

Use INDEX, XMATCH, and LET with Boolean Logic

This formula evaluates multiple conditions simultaneously using Boolean arrays alongside the LET function for efficient error handling.

Advanced Spreadsheet Functions

Master Multi-Criteria Lookups in WPS Spreadsheet

WPS Spreadsheet offers full support for modern dynamic array formulas like XLOOKUP, XMATCH, and LET. You can perform complex multi-criteria lookups just as you would in Excel, entirely for free.

1. Open WPS Spreadsheet: Launch WPS Office and open your existing spreadsheet document containing your lookup tables.
2. Select your target cell: Click the cell where you want the three-criteria lookup result to be displayed.
3. Enter your lookup formula: Type in your XLOOKUP or INDEX formula exactly as you would in Microsoft Excel.
4. Drag to fill the column: Press Enter to evaluate the result, then drag the cell handle down to instantly populate the rest of the records.

Seamlessly supports all advanced Excel formulas including XLOOKUP and LET.100% compatibility with .xlsx and .xls file formats.Free, lightweight, and fast alternative to Microsoft Office.Familiar user interface with zero learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use VLOOKUP for three criteria?

VLOOKUP is not natively designed for multi-criteria lookups across both rows and columns. While you can create a helper column to concatenate row criteria, using modern functions like XLOOKUP or INDEX/MATCH is much more efficient.

Why does my multi-criteria formula return an error?

Errors typically occur if your lookup arrays are not matching in size, if there are hidden trailing spaces in the cell text, or if there is no exact match found in the dataset. Wrapping your formula in the IFERROR function ensures a blank cell or custom text is shown instead.

Does WPS Office support the XLOOKUP function?

Yes, WPS Spreadsheet fully supports modern dynamic array functions including XLOOKUP, LET, and XMATCH, ensuring your complex multi-criteria Excel formulas work flawlessly without modification.

Excel & Data Problems