'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fill a VLOOKUP Formula Down a Column in Excel
Question details
The user needs to copy a VLOOKUP formula down an entire column to match data sets without the lookup table array shifting.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Matching lookup values (like ZIP codes) to a reference table to extract associated data (like account representatives) across multiple rows.
- Observed behavior
- When the VLOOKUP formula is dragged down without modifying the references, the table array shifts relatively, causing #N/A errors. The goal is to keep the lookup range fixed while letting the lookup value change relatively.
Ensure that your lookup table array is fully populated and that the common identifier is located in the first column of your selected range.
Lock the Lookup Table with Absolute References
Use absolute cell references (adding dollar signs) to freeze the table array so it remains constant when the formula is dragged down.
When you drag a formula down a column, spreadsheet applications automatically adjust the row numbers relative to the new cell position. While this is perfect for the lookup value, it breaks the VLOOKUP formula because the lookup table range also moves down, missing vital data.
To prevent the table array from shifting, you must lock its position using absolute references, denoted by dollar signs ($).
Select the top cell of your target column (e.g., D2) and begin typing your VLOOKUP formula: =VLOOKUP(A2,
Highlight your lookup data range (e.g., B2:C19). Immediately press the F4 key on your keyboard. This will convert the relative reference B2:C19 into the absolute reference $B$2:$C$19.
Add the column index number and the exact match indicator. Your finished formula should look like this: =VLOOKUP(A2,$B$2:$C$19,2,FALSE).
Press Enter to execute the formula. Click on the cell again, hover over the small square in the bottom-right corner (the fill handle), and either drag it down the column or double-click to auto-fill to the bottom.
Use VLOOKUP and Fill Down Effortlessly in WPS Office
WPS Spreadsheet seamlessly handles complex lookup functions just like Excel. With intelligent auto-fill capabilities and absolute reference shortcuts, processing large datasets is incredibly fast.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your tables.
- 2. Type the VLOOKUP formula: In the target cell, enter =VLOOKUP( and select your specific lookup value.
- 3. Lock references and auto-fill: Highlight the lookup array, press F4 to apply dollar signs ($), finish the formula, and double-click the cell's fill handle to apply it down the column.
Frequently Asked Questions
Why does my VLOOKUP return #N/A when I drag it down?
This happens when the table array is not locked. As you drag the formula down, the reference range moves down alongside it, eventually pointing to empty rows. You can fix this by highlighting the table array in your formula and pressing F4 to convert it to an absolute reference (e.g., changing B2:C19 to $B$2:$C$19).
How can I quickly fill the formula down to the bottom of a large dataset?
Instead of manually clicking and dragging the formula for hundreds of rows, select the cell containing your completed VLOOKUP formula and double-click the small square (fill handle) in the bottom right corner of the cell's border. The spreadsheet will automatically fill the formula down as far as there is adjacent data.
Do I need to lock both the row and column in the VLOOKUP table array?
If you are only dragging the formula down vertically, you strictly only need to lock the rows (e.g., B$2:C$19). However, locking both the rows and columns ($B$2:$C$19) is best practice as it prevents the array from shifting if you decide to copy the formula horizontally as well.
