Function Problems

How to Match Site and Part Number Across Excel Worksheets

Steve K Sep 28, 2026 869 views

Question details

The user needs to retrieve data such as Price, UOM, and Cost by matching both Site and Part Number across different worksheets where columns are not in a sequential or predictable order.

How to Match Site and Part Number Across Excel Worksheets

Product: Excel / WPS Spreadsheet
Device & OS: not provided
Scenario: Retrieving specific column values from a secondary worksheet using multiple matching criteria (Site and Part Number) simultaneously.
Observed behavior: Standard VLOOKUP fails and returns #N/A because it requires a single lookup value and a specific left-to-right column order.

Before you start

Ensure that the 'Site' and 'Part Number' data in both worksheets are formatted consistently, with no trailing spaces, hidden characters, or differing data types (e.g., numbers accidentally stored as text).

Solution 1Recommended

Use XLOOKUP for Multi-Criteria Matching

XLOOKUP is the most efficient way to match multiple conditions simultaneously without needing to rearrange your columns or create helper columns.

XLOOKUP allows you to multiply logical arrays to create a multi-criteria lookup. By looking up the value '1' (which evaluates to TRUE), it finds the row where both the Site and Part Number match.

Select the target cell

Click on the cell in your main worksheet where you want the retrieved data (e.g., Price) to appear.

Enter the XLOOKUP formula

Type the formula: =XLOOKUP(1, (Source!$A$2:$A$1000=A2)*(Source!$B$2:$B$1000=B2), Source!$C$2:$C$1000, "Not found").

Adjust the cell references

Modify the references so that Source!$A$2:$A$1000 points to your Site column, Source!$B$2:$B$1000 points to your Part Number column, and Source!$C$2:$C$1000 points to your return range (Price, UOM, or Cost).

Apply to other rows

Press Enter to retrieve the value. Select the cell again and drag the fill handle at the bottom-right corner downwards to apply the formula to the rest of the rows.

Returning Multiple Columns: If Price, UOM, and Cost are adjacent in the source sheet, you can expand the return array (e.g., Source!$C$2:$E$1000) and XLOOKUP will automatically return all three columns at once.

Solution 2

Use INDEX and MATCH as an Alternative

If your spreadsheet software version does not support XLOOKUP, INDEX and MATCH combined can achieve the exact same multi-criteria lookup.

Powerful Spreadsheet Tool

Effortlessly Manage Complex Formulas with WPS Spreadsheet

WPS Spreadsheet fully supports advanced lookup formulas like XLOOKUP, INDEX, and MATCH, making it incredibly easy to retrieve and analyze multi-criteria data across your worksheets.

1. Open your file in WPS Office: Launch WPS Spreadsheet and open the workbook containing your Site and Part Number data.
2. Select your target cell: Click the cell where you want to retrieve the matched data from the other sheet.
3. Insert the function: Navigate to the 'Formulas' tab and use the 'Insert Function' tool to effortlessly construct your XLOOKUP or INDEX/MATCH formula.
4. Apply and analyze: Hit Enter to pull the exact matching data, bypassing the limitations of traditional VLOOKUP.

Fully compatible with Microsoft Excel formulas, functions, and file formats (.xlsx)Supports advanced dynamic array functions like XLOOKUP out of the boxLightweight, fast, and completely free to use for daily data management tasks

Frequently Asked Questions

Why does VLOOKUP return #N/A even when I can see the matching data?

VLOOKUP often returns #N/A if there are extra leading or trailing spaces, hidden characters, or if the data types mismatch (e.g., one value is stored as text and the other as a number). Additionally, VLOOKUP cannot match multiple criteria natively and requires exact single-column matches.

How do I use a helper column for VLOOKUP instead of using XLOOKUP?

You can insert a new column to the left of your data in both worksheets. Combine the Site and Part Number using the ampersand operator (e.g., =A2&B2). You can then use a standard VLOOKUP to search for this combined string.

What does the '1' mean in the multi-criteria XLOOKUP formula?

The '1' acts as the lookup value representing TRUE. The formula multiplies the logical arrays (Site=Site and Part=Part). In spreadsheet logic, TRUE*TRUE equals 1. Therefore, XLOOKUP searches for the row that returns 1, meaning both conditions were met.

Is XLOOKUP available in all spreadsheet software?

XLOOKUP is available in newer versions of Excel (Microsoft 365, Excel 2021) and modern alternatives like WPS Office. If you are using an older version like Excel 2016, you will need to use the INDEX and MATCH method instead.

Excel & Data Problems