How to Populate a Table Cell from Another Table using XLOOKUP
Question details
The user needs to automatically fetch and display a specific value from a source table into another table based on a selected value, without causing #SPILL! errors.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Pulling corresponding data (like a talk outline title) from a secondary data table into a primary table based on a value chosen via a dropdown list.
- Observed behavior
- Using XLOOKUP with structured references accurately maps and returns the matched text to the current row, avoiding the spilled array issues common in older lookup array formulas.
Ensure both your source data and destination tables are clearly defined, and verify that your spreadsheet software is updated to a version that supports the XLOOKUP function.
Use XLOOKUP with Structured Table References
Best for formatted tables in Excel or WPS Spreadsheet to automatically populate rows and prevent #SPILL! errors.
Using structured references with the @ symbol ensures that the lookup targets only the current row's value. This is crucial for avoiding array spillage issues when working within formatted tables.
Click the cell in your destination table where you want the fetched data to appear.
Type the formula =XLOOKUP([@Outline],Outlines[Outline],Outlines[Title],"") into the formula bar. Replace [@Outline] with your lookup value column, Outlines[Outline] with your source lookup column, and Outlines[Title] with the return array.
Press Enter to apply. Because it is a structured table, the formula will automatically populate the data for all rows in that column.
Use XLOOKUP with Standard Range References
Ideal for Google Sheets or when not using strictly formatted Tables.
Easily Manage Table Data with XLOOKUP in WPS Office
WPS Spreadsheet provides full support for advanced functions like XLOOKUP, dynamic arrays, and structured table references. You can seamlessly map, analyze, and populate complex datasets without compatibility issues.
- 1. Open Your Workbook: Launch WPS Spreadsheet and open the file containing your tables.
- 2. Select the Target Cell: Click on the destination cell in your table where you want the cross-referenced data.
- 3. Construct the XLOOKUP Formula: Type =XLOOKUP( and select your lookup value, lookup array, and return array directly using the mouse to prevent typos.
- 4. Execute the Function: Hit Enter to execute the function and instantly fetch your data across the table.
Frequently Asked Questions
Why am I getting a #SPILL! error with XLOOKUP in my table?
A #SPILL! error occurs when an array formula tries to output multiple values but neighbor cells are blocked, or when array formulas are used inappropriately inside a formatted table. Using the @ symbol (e.g., [@ColumnName]) restricts the formula calculation to the current row, effectively preventing this error.
What happens if XLOOKUP doesn't find a match?
By default, XLOOKUP will return an #N/A error. You can handle this gracefully by filling the optional fourth argument (if_not_found) with empty quotes "" to leave the cell blank, or with a custom text like "Not Found".
Can I use XLOOKUP across different worksheets?
Yes. You can reference lookup and return arrays on entirely different worksheets by navigating to the target sheet while writing the formula, or by manually typing the sheet name followed by an exclamation mark before the range, like 'Sheet2'!A:A.
Is XLOOKUP available in older versions of Excel?
XLOOKUP is available in Microsoft 365, Excel 2021, and modern versions of WPS Office. If you are using an older version like Excel 2016 or 2019, you will need to use INDEX and MATCH or VLOOKUP to achieve similar results.
