How to Compare Two Lists and Return Matching Values in WPS Office Spreadsheets
Question details
The user needs to compare two lists or datasets in a spreadsheet and return the corresponding value when a match is found.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has two lists or datasets in one or more spreadsheets and wants to look up each item in one list against the other to return a corresponding value when a match exists.
- Observed behavior
- The lookup formula should return the matching value when found and a clear message such as "Match not found" when no match exists.
Make sure the two lists are available in the same workbook or in workbooks you can reference, and identify which column in the source list contains the values you want to return. This prevents range errors and helps you choose between XLOOKUP, VLOOKUP, or INDEX MATCH.
Use XLOOKUP to Compare Two Lists and Return Matching Values
Best for current WPS Office and Excel versions that support XLOOKUP; it looks up values in one list and returns the corresponding value or a custom no-match message.
XLOOKUP performs an exact match by default and does not require the lookup column to be the leftmost column. Use this method when your spreadsheet application supports XLOOKUP.
Click the first cell in the second list where you want the matching value to appear, for example C2.
Type =XLOOKUP(A2,ListA!$A:$A,ListA!$B:$B,"Match not found") and press Enter. Replace A2 with the first lookup value, ListA with the source sheet, and column B with the column to return.
Drag the fill handle from the bottom-right corner of the formula cell down through all items in the second list to compare the entire list.
Check that matching items show the corresponding value and non-matching items show "Match not found" or your custom text.
Use VLOOKUP with IFERROR for Older WPS or Excel Versions
Use this when XLOOKUP is not available or when you need a familiar VLOOKUP workflow with no-match handling.
Use INDEX and MATCH When the Lookup Column Is Not the Leftmost Column
Use this flexible method when the value you want to return is located before the lookup column or when you prefer a non-volatile lookup formula.
Compare Two Lists and Return Matching Values with WPS Office Spreadsheets
Use WPS Office Spreadsheets to compare two lists and return matching values with XLOOKUP, VLOOKUP, or INDEX MATCH. WPS Office is free, lightweight, and fully compatible with Excel .xlsx workbooks and formulas, so you can complete this task without changing tools.
- 1. Open your workbook in WPS Office Spreadsheets: Double-click the .xlsx file or open WPS Office, click Menu > Open, and select the workbook containing the two lists.
- 2. Select the result cell: Click the cell on the second list where you want the matching value to appear, for example C2.
- 3. Enter the XLOOKUP formula: Type =XLOOKUP(A2,ListA!$A:$A,ListA!$B:$B,"Match not found") and press Enter. Adjust the sheet names and ranges to match your workbook.
- 4. Fill the formula down: Drag the fill handle from the bottom-right corner of the result cell down through the remaining items to compare the full list.
- 5. Verify matches and no-match messages: Review the returned values; no-match cells display "Match not found" or your custom text.
Frequently Asked Questions
Does WPS Office Spreadsheets support XLOOKUP for comparing two lists?
Yes. Recent WPS Office versions support XLOOKUP. If your version returns #NAME? for XLOOKUP, update WPS Office or use VLOOKUP with IFERROR or INDEX MATCH instead.
How do I compare two lists in different workbooks in WPS Office?
Open both workbooks, then in the formula reference the other workbook. For example, in the result cell type =XLOOKUP(A2,[Book2.xlsx]Sheet1!$A:$A,[Book2.xlsx]Sheet1!$B:$B,"Match not found") and press Enter.
How do I return "Match not found" when a value is missing?
Use the fourth argument of XLOOKUP, such as =XLOOKUP(A2,ListA!$A:$A,ListA!$B:$B,"Match not found"), or wrap VLOOKUP in IFERROR: =IFERROR(VLOOKUP(A2,ListA!$A:$B,2,FALSE),"Match not found").
What formula should I use if the matching value is not in the leftmost column?
Use INDEX and MATCH: =INDEX(ListA!$B:$B,MATCH(A2,ListA!$A:$A,0)). This works regardless of column order.
Can I compare two lists without formulas in WPS Office?
You can use Conditional Formatting to highlight duplicate or unique values visually, but formulas such as XLOOKUP, VLOOKUP, or INDEX MATCH are required to return the corresponding value from another column.
