How to Look Up a Value From Another Worksheet in Excel
Question details
The user needs to match a value in a column against a column on a different worksheet and return the corresponding adjacent value into the current worksheet.
- Product
- Excel / WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Comparing and merging datasets across different worksheets by looking up matching identifier values to pull relevant data into a master sheet.
- Observed behavior
- A formula is required to automate cross-sheet data retrieval based on matching column values without manually copying and pasting.
Ensure that the columns you are using to match data in both worksheets share the same data type (e.g., both are formatted as text or both as numbers) and contain no trailing spaces.
Use the XLOOKUP Function to Retrieve Data Across Sheets
XLOOKUP is the most modern, flexible, and robust function for finding a value in one column and returning a corresponding value from another worksheet.
The XLOOKUP function simplifies cross-sheet lookups by allowing you to independently select the lookup array and the return array. It also natively handles errors if a match is not found.
Navigate to the sheet where you want to display the result and click on the destination cell (for example, cell B2).
Type `=XLOOKUP(` to begin the function, then click on the cell containing your lookup value (for example, A2) and type a comma.
Click the tab of your source worksheet (e.g., Sheet1), select the entire column containing the values to match against (e.g., Column A), and type a comma.
On the same source worksheet, select the column containing the data you want to retrieve (e.g., Column B). Type `,"")` to tell the formula to return a blank cell if no match is found, then press Enter.
Click the cell with your completed formula (e.g., `=XLOOKUP(A2,Sheet1!A:A,Sheet1!B:B,"")`), hover over the bottom-right corner until you see the fill handle, and drag it down to apply it to the remaining rows.
Use the VLOOKUP Function (Alternative for Older Versions)
If you are using an older version of Excel that does not support XLOOKUP, VLOOKUP is the standard alternative for vertical data matching.
Easily Manage Data Across Sheets with WPS Spreadsheet
WPS Spreadsheet provides seamless support for advanced functions like XLOOKUP and VLOOKUP, making cross-sheet data analysis effortless. It offers a familiar interface and total file compatibility, ensuring your formulas work perfectly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your multiple worksheets.
- 2. Insert the lookup function: Select the target cell, navigate to the 'Formulas' tab, and click 'Insert Function' to easily search for and select XLOOKUP.
- 3. Use the formula dialog: Follow the intuitive Function Arguments dialog box to visually select your lookup value, the lookup array on the other sheet, and your return array without typing syntax manually.
- 4. Complete your data merge: Click 'OK' to fetch the value, and drag the fill handle to apply the formula down your dataset instantly.
Frequently Asked Questions
Why is my lookup formula returning an #N/A error?
The #N/A error occurs when the formula cannot find an exact match for your lookup value. Check for hidden spaces, formatting discrepancies (like numbers formatted as text), or verify that the lookup value actually exists in the source sheet's column.
Can I use XLOOKUP to return values to the left of the lookup column?
Yes. Unlike VLOOKUP, which only searches left-to-right, XLOOKUP can look up a value in any column and return a corresponding value from any other column, regardless of its position.
How do I look up data from a completely different workbook file?
You can reference another workbook by keeping both files open. When building your formula, simply click into the other workbook to select the ranges. The formula will automatically add the file name in square brackets, such as ='[Data.xlsx]Sheet1'!A:A.
What does the empty string ("") do at the end of the XLOOKUP formula?
Adding an empty string ("") as the fourth argument in the XLOOKUP function tells the spreadsheet to return a visually blank cell instead of displaying an ugly #N/A error when a lookup value is not found.
