How to Use a Lookup Formula to Return Data from Another Worksheet in WPS Office Spreadsheets
Question details
The user needs a lookup formula that searches an entire range on another worksheet and returns the matching value from a different column, rather than evaluating only the corresponding row.
- Product
- WPS Office Spreadsheets (Excel 2019-compatible)
- Device & OS
- not provided
- Scenario
- A user has a value in one worksheet and wants to look it up in a range on another worksheet to return the matching Vendor 2 date-range value.
- Observed behavior
- A formula such as =IF(A8=Sheet2!C8:C300,7,"") evaluates only the corresponding row on the other sheet instead of searching the entire range, so the expected value is not returned.
Confirm the source worksheet name and the exact lookup column and return column ranges. If the source data is in a separate workbook, open it in WPS Office so the formula can reference it correctly.
Replace the direct range comparison with an INDEX/MATCH lookup formula
Use this method when you need Excel 2019 or WPS Office Spreadsheets to search the entire lookup range and return the matching value from any row.
A direct comparison such as =IF(A8=Sheet2!C8:C300,7,"") is evaluated row by row, so it cannot perform a true lookup across a range. INDEX and MATCH work together to find the position of the lookup value anywhere in the range and return the value from the same row in the return column.
Click the cell where you want the returned Vendor 2 date-range value to appear.
Type =IFERROR(INDEX(Sheet2!$D$2:$D$300,MATCH($A8,Sheet2!$C$2:$C$300,0)),"") and press Enter.
Replace Sheet2, D2:D300, C2:C300, and A8 with the actual source sheet name, return column, lookup column, and lookup cell in your workbook.
Check that the formula returns the expected value from the other worksheet, such as the Vendor 2 date-range result, and copy the formula down as needed.
Use VLOOKUP as an alternative when the return column is to the right of the lookup column
Choose VLOOKUP if the value you want to return is located to the right of the looked-up column and you prefer a shorter formula.
Create Cross-Sheet Lookup Formulas in WPS Office Spreadsheets
WPS Office Spreadsheets supports INDEX/MATCH and VLOOKUP across worksheets, so you can fix this Excel 2019 lookup issue directly without changing your workbook. Open the file, replace the direct range comparison, and let WPS return the correct value from another sheet.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the Excel 2019 workbook that contains the lookup formula.
- 2. Select the result cell: Click the cell where the returned Vendor 2 date-range value should appear and make the formula bar active.
- 3. Enter the cross-sheet lookup formula: Type =IFERROR(INDEX(Sheet2!$D$2:$D$300,MATCH($A8,Sheet2!$C$2:$C$300,0)),"") and adjust the sheet and column references to match your data.
- 4. Save and reuse the workbook: Press Enter, verify the returned value, and save the file as .xlsx to keep it compatible with Excel 2019.
Frequently Asked Questions
Why does =IF(A8=Sheet2!C8:C300,7,"") only match the same row?
In Excel 2019 and WPS Office Spreadsheets, a direct comparison against a range is evaluated with implicit intersection by default, so it checks the corresponding row rather than searching all rows. Use INDEX/MATCH or VLOOKUP to search the entire range.
Can I return a value from another worksheet without opening the other workbook?
If the source data is in another workbook, you can reference it by including the workbook name in brackets, such as [Book2.xlsx]Sheet2!$C$2:$C$300, but the source workbook must be open or the formula may return an error until WPS recalculates with access.
How do I hide lookup errors when no match is found?
Wrap the formula in IFERROR, for example =IFERROR(INDEX(Sheet2!$D$2:$D$300,MATCH($A8,Sheet2!$C$2:$C$300,0)),""), so it returns an empty string instead of #N/A.
Will these formulas work when I open the file in Excel 2019 later?
Yes, INDEX/MATCH and VLOOKUP are standard Excel formulas. WPS Office Spreadsheets saves in .xlsx format, so the formulas continue to work in Excel 2019.
What does the 0 at the end of MATCH mean?
The 0 requests an exact match, so MATCH finds the first cell in the lookup range exactly equal to the lookup value and returns its position to INDEX.
