How to Return Matching Data From Another Worksheet in WPS Office Excel with VLOOKUP and XLOOKUP
Question details
The user wants to enter a lookup number on one worksheet and return the corresponding description from a table on another worksheet using a lookup formula such as VLOOKUP or XLOOKUP.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- Creating a cross-sheet lookup so a value entered in one worksheet automatically pulls the matching description from a source table in another worksheet.
- Observed behavior
- The goal is to have an exact-match formula automatically return the matching description from another worksheet without manual searching or copy-paste.
Open the workbook in WPS Office Excel and verify that the source data range on Sheet1 has the lookup values in the first column and the descriptions in the adjacent column.
Use VLOOKUP to Return an Exact Match from Another Worksheet
Choose this method for any WPS Office Excel version when the lookup value is in the first column of the source range and you need an exact match.
VLOOKUP scans the first column of the source range for the lookup value and returns the value from a specified column to the right. The FALSE argument disables approximate matching, so the formula only returns a result when the entered number exactly matches an entry in Sheet1.
Open the workbook in WPS Office Excel and click Sheet2 cell C3, the cell where the matching description should appear.
Type =VLOOKUP(C2,Sheet1!$A$2:$B$4,2,FALSE) and press Enter. In this formula, C2 is the lookup number, Sheet1!$A$2:$B$4 is the source table, 2 is the column to return, and FALSE requires an exact match.
Type 1 in Sheet2 cell C2. The formula returns Work when Sheet1 column A contains 1 and column B contains Work. If no matching value is found, WPS Office Excel displays #N/A.
Use XLOOKUP for a Flexible Cross-Worksheet Lookup
Use this method if your WPS Office Excel version supports XLOOKUP and you want separate lookup and return ranges plus a custom not-found result.
Return Matching Data Across Worksheets in WPS Office Excel
Create a cross-worksheet lookup in WPS Office Excel using VLOOKUP or XLOOKUP. Enter a value on one sheet and automatically return the matching description from a table on another sheet, with no manual search or copy-paste.
- 1. Download and install WPS Office: Get WPS Office Free from the official WPS website and install it on your Windows, macOS, Linux, or mobile device.
- 2. Open the Excel workbook: Launch WPS Office Excel and open the .xlsx workbook that contains your source table and target sheet.
- 3. Enter a lookup formula: Click the result cell and enter =VLOOKUP(C2,Sheet1!$A$2:$B$4,2,FALSE) or =XLOOKUP(C2,Sheet1!$A$2:$A$4,Sheet1!$B$2:$B$4,"Not found"), then press Enter.
- 4. Test the returned value: Type the lookup number in cell C2 and confirm that the matching description appears automatically from the other worksheet.
Frequently Asked Questions
Why does VLOOKUP return #N/A even though the lookup value is in the sheet?
The usual causes are an extra space, a number stored as text, or a lookup range that does not have the value in its first column. Use FALSE for exact match, check that the data types match, and confirm the lookup value is in the first column of the source range.
Can I return matching data from a worksheet whose name contains spaces?
Yes. In WPS Office Excel, wrap the sheet name in single quotes in the formula, for example =VLOOKUP(C2,'Sales Data'!$A$2:$B$20,2,FALSE).
What is the difference between VLOOKUP and XLOOKUP for this task?
VLOOKUP searches the first column of a range and returns a value from a column to the right, and it needs FALSE for an exact match. XLOOKUP uses separate lookup and return arrays, can search left or right, and supports a custom not-found message such as "Not found".
How do I copy the lookup formula without changing the source range?
Lock the source range with absolute references, such as $A$2:$B$4. The lookup cell reference can remain relative, such as C2, so it updates correctly when you copy the formula down or across.
