Function Problems

How to Retrieve a Matching Value from Another Worksheet in Excel

Ayan Masood Oct 21, 2026 868 views

Question details

The user needs to match a value from one sheet against a column in another sheet and retrieve the corresponding value from a different column, but encounters issues where valid matches return blank.

How to Retrieve a Matching Value from Another Worksheet in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Looking up data across different worksheets using reference formulas like XLOOKUP or INDEX/MATCH.
Observed behavior: Formulas return blank or no result even when the lookup value visually appears to exist in the source data array on the other worksheet.

Before you start

Ensure both worksheets are in the same workbook and check that the column containing your lookup values does not have trailing spaces or inconsistent text-to-number formatting.

Solution 1Recommended

Use the XLOOKUP Function (Excel 2021 and Microsoft 365)

XLOOKUP is the most robust and straightforward formula for looking up data horizontally and vertically across worksheets.

XLOOKUP replaces older functions like VLOOKUP and HLOOKUP. It allows you to look up a value in any column and return a corresponding value from any other column, even if it is to the left.

Select the destination cell

Click on the cell in Sheet1 where you want the retrieved value to appear.

Enter the XLOOKUP formula

Type =XLOOKUP(B1, 'Sheet 2'!$J$1:$J$1000, 'Sheet 2'!$B$2:$B$1000, "") into the formula bar.

Apply the formula

Press Enter. This tells Excel to look for the value of B1 in Column J of Sheet 2 and return the corresponding value from Column B. If no match is found, it returns a blank cell.

Absolute References: Using dollar signs ($) locks the lookup ranges so you can safely drag the formula down to apply it to other rows without shifting the referenced area.

Solution 2

Use INDEX and MATCH (For Older Excel Versions)

If you are using Excel 2019 or older, XLOOKUP is not available. Using INDEX and MATCH together provides the exact same functionality.

Solution 3

Troubleshoot Data Type Mismatches and Hidden Spaces

If your formula returns a blank despite the value being visibly present, the data types might be different or contain invisible spaces.

Effortlessly Lookup Data Across Sheets with WPS Spreadsheet

WPS Office provides powerful data analysis tools, fully supporting advanced functions like XLOOKUP, INDEX, and MATCH to help you retrieve matching values seamlessly across multiple worksheets.

1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your data.
2. Select the target cell: Click the cell on your primary sheet where you want to retrieve the value.
3. Insert the lookup function: Go to the Formulas tab and select 'Insert Function', or directly type =XLOOKUP() into the formula bar.
4. Select your arrays: Input your lookup value, highlight the lookup array on the second sheet, select the return array, and press Enter.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Supports advanced lookup functions including XLOOKUP and VLOOKUPBuilt-in error checking for data mismatches and formatting issuesLightweight, fast, and completely free to use

microsoft office alternative - wps office

Frequently Asked Questions

Why does my lookup formula return a blank cell or #N/A?

This usually happens if the exact value doesn't exist in the lookup array, or if there is a data mismatch. Check for hidden spaces, ensure both columns are either text or numbers, and verify that your range references haven't shifted by using absolute references (dollar signs).

Can I use VLOOKUP instead of XLOOKUP to pull data from another sheet?

Yes, but VLOOKUP requires the return value to be to the right of your lookup column. In this scenario, where you search in Column J and return from Column B (which is to the left), VLOOKUP will not work unless you physically rearrange your columns. XLOOKUP and INDEX/MATCH handle leftward lookups easily.

How do I reference a sheet name that contains spaces in a formula?

If your worksheet name contains spaces, you must enclose the sheet name in single quotation marks within your formula. For example, you must use 'Sheet 2'!$A$1 instead of Sheet 2!$A$1.

Excel & Data Problems