Function Problems

How to Use XLOOKUP, INDEX, and MATCH Across Sheets with Multiple Criteria in Excel

John Wilson Oct 9, 2026 869 views

Question details

The user needs to match multiple criteria (e.g., clothing and condition) on a separate Data sheet, return two corresponding codes, and combine them with an existing cell value in the main sheet.

How to Use XLOOKUP, INDEX, and MATCH Across Sheets with Multiple Criteria

Product: Excel
Device & OS: not provided
Scenario: Looking up matching data across multiple sheets using two or more criteria to return concatenated values.
Observed behavior: Requires a formula combination utilizing XLOOKUP or INDEX/MATCH to pull the correct matched codes and join them with a cell from the current worksheet without throwing reference errors.

Before you start

Ensure both your main sheet and your Data sheet are in the same workbook. Verify that the ranges for your lookup criteria columns match exactly in row size to prevent array calculation errors.

Solution 1Recommended

Use XLOOKUP with Concatenated Criteria (Modern Excel)

The XLOOKUP function simplifies multi-criteria lookups by allowing you to concatenate multiple lookup values and lookup arrays natively using the ampersand (&) operator.

This method is highly recommended if you are using Microsoft 365, Excel 2021, or later, as XLOOKUP handles arrays natively without needing special key combinations.

Select the target cell

Click on the cell in Sheet1 (e.g., F3) where you want the combined result to be displayed.

Combine the initial cell value

Start your formula by linking the required value in Column A using the ampersand: `=A3&`

Write the first XLOOKUP formula

Add the first XLOOKUP to match the clothing and condition criteria on the Data sheet: `XLOOKUP(B3&C3, Data!$A$2:$A$110&Data!$C$2:$C$110, Data!$B$2:$B$110)`

Append the second XLOOKUP formula

Add another ampersand and append the second XLOOKUP to return the second code: `&XLOOKUP(B3&C3, Data!$A$2:$A$110&Data!$C$2:$C$110, Data!$D$2:$D$110)`

Execute the formula

Press the Enter key. The full formula should look like: `=A3&XLOOKUP(...)&XLOOKUP(...)`, returning the combined string from all three sources.

Use XLOOKUP with Concatenated Criteria (Modern Excel)

Absolute References: Using dollar signs ($A$2:$A$110) locks the cell ranges so the formula continues to work flawlessly when you drag it down to other rows.

Solution 2

Use INDEX and MATCH with Array Formulas (Older Excel Versions)

For older versions of Excel that do not support XLOOKUP, use the INDEX and MATCH combination by multiplying the condition arrays to simulate an AND logic operation.

Use WPS Spreadsheet for Advanced Multi-Sheet Lookups

WPS Spreadsheet fully supports advanced lookup functions like XLOOKUP, INDEX, and MATCH, making it incredibly easy to pull and concatenate data across multiple sheets. It seamlessly handles complex array formulas while maintaining a familiar user interface.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the file containing your multiple sheets.
2. Enter the lookup formula: Click on the target cell and type your multi-criteria lookup: `=XLOOKUP(B3&C3, Data!$A$2:$A$110&Data!$C$2:$C$110, Data!$B$2:$B$110)`
3. Concatenate values: Use the '&' operator to append other lookups or static cell references, such as `=A3&XLOOKUP(...)`.
4. View combined data instantly: Press Enter to execute the formula and drag the fill handle down to apply the lookup across multiple rows.

100% compatible with Microsoft Excel (.xlsx) formats, ensuring formulas work seamlessly.Natively supports modern XLOOKUP for easy multi-criteria searches without array hotkeys.Lightweight, free alternative to Microsoft Office that is easy to install and run.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my multi-criteria INDEX and MATCH formula return an error?

In older versions of Excel, an INDEX MATCH formula evaluating multiple criteria by multiplication is considered an array formula. You must confirm the formula by pressing Ctrl + Shift + Enter rather than just Enter. Additionally, verify that all lookup arrays (e.g., $A$2:$A$110 and $C$2:$C$110) are exactly the same size.

Can I use spaces or delimiters when concatenating lookup results?

Yes. You can add delimiters like a hyphen or a space by placing them inside quotation marks and connecting them with ampersands. For example: `=A3 & " - " & XLOOKUP(...)` will place a dash between your first value and the lookup result.

Does XLOOKUP work across different workbooks?

Yes, XLOOKUP can pull data from an entirely different workbook. Simply keep both workbooks open when writing the formula so you can easily click to select the ranges. The references will update to include the external file path.

How do I make sure my lookup ranges don't shift when copying the formula down?

You need to use absolute cell references. Add dollar signs ($) before the column letters and row numbers (e.g., $A$2:$A$110). You can easily convert a relative range to an absolute range by highlighting the reference in the formula bar and pressing the F4 key.

Excel & Data Problems