How to Keep Excel Lookup Ranges Fixed When Copying Formulas
Question details
The user needs to prevent the lookup and return ranges from changing when copying or dragging a LOOKUP formula down multiple rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Copying or dragging a lookup formula down a column to apply it to multiple cells.
- Observed behavior
- By default, the relative references change the row numbers of the lookup range, which shifts the search area and causes incorrect returns.
Open your spreadsheet and click on the cell containing your lookup formula. Identify the exact cell range that acts as your lookup array and return array before applying the lock.
Lock Formula Ranges Using the F4 Shortcut
The fastest way to lock a range in Excel is by converting relative references to absolute references using the F4 key.
In Excel, adding dollar signs ($) to a cell reference converts it into an absolute reference, which tells the program to keep that specific row or column constant regardless of where the formula is copied. The F4 key acts as a quick toggle for this syntax.
Double-click the cell containing your formula, or select the cell and click inside the Formula Bar at the top of the screen.
Highlight the lookup range reference in the formula text (for example, select K40:K44).
Press the F4 key on your keyboard. The reference will automatically update to include dollar signs (e.g., $K$40:$K$44).
Repeat the highlighting and F4 process for your return array range (e.g., change L40:L44 to $L$40:$L$44), then press Enter to apply.
Manually Add Dollar Signs to Lock References
If your keyboard lacks an F4 key or it is mapped to other media functions, you can type the absolute reference syntax manually.
Effortlessly Manage Complex Formulas in WPS Spreadsheet
WPS Spreadsheet offers full support for advanced lookup functions and absolute references, making data analysis highly intuitive. It provides a familiar interface to easily lock cell ranges while offering excellent compatibility with Excel files.
- 1. Open Your File: Open your Excel file (.xlsx or .xls) directly in WPS Spreadsheet.
- 2. Enter the Formula: Type your lookup formula and highlight the range reference you want to fix in the formula bar.
- 3. Lock the Range: Press F4 to instantly convert the selection to an absolute reference with dollar signs.
- 4. Fill Down: Drag the cell's fill handle down your column to apply the locked formula to the rest of your data.
Frequently Asked Questions
Why does my VLOOKUP return #N/A when I drag it down?
This usually happens because the table array range is using relative references. When you drag the formula down, the search range shifts down as well, causing it to miss the actual data. Locking the table array with absolute references (e.g., $A$2:$D$100) prevents this shift.
What is the difference between $K40 and K$40?
A reference like $K40 is a mixed reference that locks only column K, meaning the column will not change if you copy the formula horizontally, but the row will change vertically. Conversely, K$40 locks only row 40, meaning the row stays the same vertically, but the column changes when copied horizontally.
How do I unlock a range after locking it?
You can unlock a range by selecting the reference in the formula bar and pressing the F4 key repeatedly until all dollar signs are removed. Alternatively, you can just manually delete the dollar signs from the formula text.
Does the F4 shortcut work on Mac?
On a Mac, the equivalent shortcut to toggle absolute and relative references in Excel is Command + T. However, depending on your Apple keyboard settings, pressing F4 (or fn + F4) may also work.
