How to Fix XLOOKUP Formula Errors When Copied Across Columns
Question details
The user needs to fix an XLOOKUP formula combined with OFFSET that returns errors or incorrect results when copied horizontally across columns.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Copying a complex lookup formula across multiple columns where the reference range needs to shift dynamically by a specific number of columns (e.g., two columns).
- Observed behavior
- The formula works in the first column but returns errors (such as #N/A or circular reference warnings) or incorrect data in subsequent columns due to improperly shifting references.
Before adjusting your formulas, ensure your lookup arrays and return arrays are explicitly defined, and check whether standard absolute references ($A$1) would suffice instead of dynamic offsets.
Lock References and Simplify the OFFSET Logic
Correctly apply absolute references to prevent unintended shifting and use a dynamic column counter for the OFFSET function.
When copying formulas across columns, relative references (like A4) will automatically shift to B4, C4, etc. If this is unintended, you must lock them using absolute references (like $A$4). Additionally, simplifying the offset logic ensures the entire lookup and return arrays shift uniformly.
Click on the cell containing your initial XLOOKUP formula.
Check your lookup value reference. If it shouldn't shift when copied to the right, change relative references like A4 to a column-locked reference like $A4.
Modify your OFFSET function to shift the return and lookup arrays dynamically. Use the COLUMN() function to calculate the offset multiplier. Ensure the row offset is set to 0 so the rows do not shift down.
Input the simplified formula. For example: =XLOOKUP($A4, OFFSET('XLOOKUP page'!$B$4:$B$540, 0, COLUMN(A1)*2-2), OFFSET('XLOOKUP page'!$A$4:$A$540, 0, COLUMN(A1)*2-2))
Click and drag the fill handle horizontally to apply the corrected formula across your target columns.
Resolve Circular References and Row Misalignments
Identify if the formula is accidentally referencing its own cell or pulling data from the wrong rows.
Use WPS Spreadsheet to Master Advanced Formulas
WPS Spreadsheet fully supports advanced functions like XLOOKUP, OFFSET, and dynamic arrays. It provides an intuitive formula editing interface with built-in error checking to help you build complex data models effortlessly.
- 1. Open your workbook: Launch WPS Spreadsheet and open the workbook containing your data.
- 2. Select the target cell: Click on the cell where you want to insert the XLOOKUP function.
- 3. Open the formula builder: Go to the Formulas tab and click Insert Function to open the formula builder dialog.
- 4. Configure XLOOKUP: Search for XLOOKUP and use the intuitive dialog box to select your lookup value, lookup array, and return array.
- 5. Toggle absolute references: Highlight your cell references in the formula bar and press the F4 key to quickly toggle absolute and relative references before dragging your formula across columns.
Frequently Asked Questions
Why does my XLOOKUP return #N/A when copied to the right?
This usually happens because your lookup array or return array uses relative references. When you drag the formula to the right, the reference ranges also shift right, causing the formula to look in empty or incorrect columns. Fix this by locking the ranges using $ signs (e.g., $B$2:$B$100).
Can I use XLOOKUP to return multiple columns at once instead of using OFFSET?
Yes. If you simply want to return an array of adjacent columns, you can select a multi-column range for your return array (e.g., C2:E100). XLOOKUP will automatically spill the results across multiple columns without needing the OFFSET function.
What does the COLUMN function do inside an OFFSET formula?
The COLUMN function returns the column number of a reference. When combined with math operations (like COLUMN(A1)*2), it creates a dynamic counter that automatically increments the column offset by a specific multiple as you drag the formula to the right.
How do I easily toggle absolute references when typing a formula?
You can highlight the cell reference in the formula bar and press the F4 key. Pressing it repeatedly cycles through different locking modes: fully locked ($A$1), row locked (A$1), column locked ($A1), and unlocked (A1).
