How to Fill an Excel Column from a Matching Value in a Reference Table in WPS Office
Question details
The user needs to return a value from a reference sheet and fill a column in a large Excel data table by matching an item number or other key.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Working in a large Excel data table, the user wants to populate one column with values from a separate reference table based on a matching key such as an item number.
- Observed behavior
- The target column needs to be filled automatically with the matching value for each row instead of being entered manually.
Check that the data table and reference table are in the same workbook, and confirm the key column exists in both. Also verify that your WPS Office version supports XLOOKUP; if not, plan to use VLOOKUP or INDEX/MATCH.
Use XLOOKUP with structured references
Best for current WPS Office versions when the data range is formatted as an Excel table; it returns exact matches and can look to any column.
XLOOKUP searches the current row's item number in the reference sheet and returns the corresponding value from the column you specify. Structured references such as [@Itemnumber] make the formula readable and automatically apply to each table row.
Select the first cell in the column where the returned value should appear, such as D2 inside the Excel table.
Enter =XLOOKUP([@Itemnumber],'Tab2'!$A$2:$A$600,'Tab2'!$C$2:$C$600) and adjust 'Tab2', the lookup range, and the return range to your workbook.
Press Enter, then double-click the fill handle at the lower-right corner of the cell to copy the formula to the remaining rows.
Use VLOOKUP for older WPS Office or Excel versions
Use this method when XLOOKUP is not available in your version, or when you need a straightforward exact-match lookup.
Use INDEX and MATCH when the return column is to the left of the lookup column
Choose this method if you need to return a value from a column that is located to the left of the key column, which VLOOKUP cannot do.
Fill Excel Columns from a Reference Table in WPS Office Spreadsheets
WPS Office Spreadsheets can perform the same lookup task with XLOOKUP, VLOOKUP, or INDEX/MATCH. Open your workbook in WPS Office, enter the lookup formula, and fill the entire column without manually copying values.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open the Excel workbook that contains the data table and reference sheet.
- 2. Select the first target cell: Click the first cell in the column you want to fill with matching reference values.
- 3. Enter the lookup formula: Type =XLOOKUP([@Itemnumber],'Tab2'!$A$2:$A$600,'Tab2'!$C$2:$C$600) and adjust the sheet name, lookup range, and return range to your file.
- 4. Fill the whole column: Press Enter, then double-click the fill handle at the lower-right corner of the cell to apply the formula to the remaining rows.
Frequently Asked Questions
Does WPS Office support XLOOKUP?
Yes, current versions of WPS Office Spreadsheets support XLOOKUP. If the function is not available, update WPS Office or use VLOOKUP or INDEX/MATCH as an alternative.
Why does my XLOOKUP formula return #N/A?
#N/A means the lookup value was not found in the reference column. Check that the item number is exactly the same in both sheets, with no extra spaces, or wrap the formula with IFERROR to return a custom value.
Can I use structured references such as [@Itemnumber] in WPS Spreadsheets?
Yes. Select the data range, press Ctrl+T to format it as an Excel table, and then use the column name in a structured reference like [@Itemnumber].
How do I quickly fill the formula down thousands of rows?
After entering the formula in the first cell and pressing Enter, double-click the fill handle at the lower-right corner of the cell. You can also select the target range and press Ctrl+D to fill down.
What if my return column is to the left of the lookup column?
Use INDEX and MATCH instead of VLOOKUP. For example, =INDEX('Tab2'!$C$2:$C$600,MATCH(A2,'Tab2'!$A$2:$A$600,0)) can return a value from column C while looking up column A.
