Use XLOOKUP to Compare Excel Worksheets and Return a Matching Value
Question details
The user needs to compare column A of a Users worksheet with column A of a LastLogon worksheet using XLOOKUP. When a match is found, the value from column C of LastLogon should be returned to column H of Users.
- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is working with two worksheets in a workbook and wants to populate column H with matching logon data from another sheet.
- Observed behavior
- XLOOKUP should return the value from column C of the LastLogon worksheet when column A values match. If no match is found, the formula should return a blank instead of an error.
Open the workbook in WPS Office Spreadsheet and verify that both worksheets contain the lookup IDs in column A. Make sure row 1 is available for headers so you can start the formula in H2 and fill it down without overwriting data.
Use XLOOKUP to return a matching value from another worksheet
Use this solution when the lookup values are in column A of the Users worksheet and the desired return values are in column C of the LastLogon worksheet.
XLOOKUP searches a lookup array and returns the corresponding value from a return array. The optional if_not_found argument lets you display a blank instead of #N/A when no match exists.
Open the workbook in WPS Office Spreadsheet, click the Users worksheet tab, and select cell H2 where the returned value should appear.
Type =XLOOKUP(A2,LastLogon!$A:$A,LastLogon!$C:$C,"") and press Enter. This matches A2 against column A of the LastLogon sheet and returns the corresponding value from column C.
With H2 selected, use the fill handle to copy the formula down through the remaining rows in column H. If the sheet name contains spaces, use apostrophes: =XLOOKUP(A2,'Last Logon'!$A:$A,'Last Logon'!$C:$C,"").
Compare worksheets with XLOOKUP in WPS Office Spreadsheet
WPS Office Spreadsheet supports XLOOKUP, so you can compare a Users worksheet with LastLogon, return matching values, and keep the workbook fully compatible with Microsoft Excel .xlsx files.
- 1. Open your workbook in WPS Office Spreadsheet: Launch WPS Office, open the workbook containing the Users and LastLogon worksheets, and switch to the Users worksheet.
- 2. Click cell H2 on the Users worksheet: Select H2 where the matching value should appear.
- 3. Enter the XLOOKUP formula: Type =XLOOKUP(A2,LastLogon!$A:$A,LastLogon!$C:$C,"") and press Enter. Adjust the sheet name with apostrophes if it contains spaces.
- 4. Fill the formula down: Drag the fill handle from H2 down through the remaining rows to compare each row.
- 5. Check the returned values: Verify that column H now shows the corresponding value from column C of LastLogon, or a blank when no match is found.
Frequently Asked Questions
What does the empty string at the end of the XLOOKUP formula do?
The final argument "" is the if_not_found value. It makes WPS Office Spreadsheet return a blank cell instead of #N/A when the value in column A on the Users sheet is not found in column A of the LastLogon sheet.
How do I reference a sheet name that contains spaces in XLOOKUP?
Enclose the sheet name in apostrophes. For example, use =XLOOKUP(A2,'Last Logon'!$A:$A,'Last Logon'!$C:$C,"") when the worksheet is named Last Logon.
Can I drag the XLOOKUP formula down column H?
Yes. In the recommended formula, A2 is a relative reference, so filling down updates it to A3, A4, and so on. The lookup and return arrays use absolute references, so they remain fixed on the LastLogon worksheet.
Do both worksheets need to be in the same workbook?
This formula works directly across worksheets in the same workbook. If the data is in a different workbook, you need to include the full workbook path or use a separate workbook reference.
Does WPS Office support XLOOKUP in spreadsheets?
Yes. WPS Office Spreadsheet supports XLOOKUP, and workbooks containing XLOOKUP formulas remain compatible with Microsoft Excel .xlsx files.
