Use XLOOKUP to Fill an Excel Column From an Index Number in WPS Spreadsheets
Question details
User needs to return the matching value in column E by looking up the index number in column A against the key column D.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has an index number in column A and wants to retrieve the corresponding value from column E based on matching values in column D.
- Observed behavior
- The expected result is a filled column B that returns the matching value from column E and displays a blank when no match is found.
Open the workbook in WPS Spreadsheets and confirm that the index numbers are in column A, the key values are in column D, and the values to return are in column E. Make sure column B is empty or that you are prepared to overwrite its cells with the lookup results.
Use XLOOKUP to Fill an Excel Column From an Index Number in WPS Spreadsheets
Best for WPS Spreadsheets versions that support XLOOKUP; it returns an exact match and displays a blank when no match exists.
The XLOOKUP function searches the key range D3:D18 for the index number in A2 and returns the corresponding value from E3:E18. The final argument "" controls the no-match result so the cell remains blank instead of showing an error.
Click cell B2 in the worksheet where the matching value should appear.
Type =XLOOKUP(A2,$D$3:$D$18,$E$3:$E$18,"") into B2 and press Enter.
Drag the fill handle from B2 down through the remaining rows in column B to apply the lookup to each index number in column A.
Use IFERROR and VLOOKUP as a Compatibility Alternative
Use this when XLOOKUP is not available or the workbook must remain compatible with older spreadsheet versions that do not recognize XLOOKUP.
Fill a Column with XLOOKUP Index Matches in WPS Spreadsheets
Open your workbook in WPS Spreadsheets, use the XLOOKUP formula to retrieve values from the return column based on the index key, and fill the formula down as needed. The same workbook remains compatible with common Excel formats.
- 1. Open the workbook: Open the target .xlsx, .xls, or .xlsm file in WPS Spreadsheets.
- 2. Click the first output cell: Select cell B2 or the first cell in the column where you want the matching value to appear.
- 3. Enter the XLOOKUP formula: Type =XLOOKUP(A2,$D$3:$D$18,$E$3:$E$18,"") and press Enter.
- 4. Fill the formula down: Drag the fill handle down the column to apply the formula to the remaining index numbers.
- 5. Review the results: Confirm that matched values appear from column E and blank cells appear where no match exists.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP?
Yes, current WPS Spreadsheets versions support XLOOKUP. Enter =XLOOKUP(lookup_value, lookup_array, return_array, if_not_found) in the target cell just as you would in Microsoft Excel.
What does the final empty string argument in the formula do?
The "" at the end of =XLOOKUP(A2,$D$3:$D$18,$E$3:$E$18,"") is the if_not_found value. It tells XLOOKUP to return a blank cell instead of an error when no match is found.
Can I use this formula with different column ranges?
Yes. Replace A2 with the cell that contains the index number, $D$3:$D$18 with the key range you are searching, and $E$3:$E$18 with the range containing the values to return.
What should I do if XLOOKUP is not recognized?
Update WPS Office to a version that supports XLOOKUP, or use the compatibility formula =IFERROR(VLOOKUP(A2,$D$3:$E$18,2,FALSE),"") and fill it down.
How do I fill the formula down without retyping it?
Click the cell with the formula, then drag the small square fill handle at the lower-right corner of the cell down through the remaining rows in column B.
