Freeze VLOOKUP Formula in Excel: Lock or Fix Results

To keep a VLOOKUP’s lookup range from shifting when you copy the formula, make that range absolute by adding dollar signs, such as $H$2:$J$100. If by freeze you mean stop the answer from changing, copy the VLOOKUP results and paste them as values instead. These are two different tasks: one keeps a formula working as it is filled, and the other removes the formulas so the current answers stay fixed.

What You’ll Need

An Excel workbook with a lookup value and a table of data. This guide uses Microsoft Excel for Microsoft 365 on Windows.
A column in the lookup table containing the values VLOOKUP should search, with the return data in one or more columns to its right.
Permission to edit the worksheet. If you plan to replace formulas with values, keep a copy of the formulas or save a backup first.

VLOOKUP itself does not have a freeze switch. Usually, people mean one of two things: locking a reference so a formula can be copied without its table range moving, or replacing a formula with its current result. The steps below cover both meanings. Freezing panes, which keeps rows or columns visible while scrolling, is a separate Excel feature and does not affect lookup formulas.

Step-by-Step

Check the layout of your data. Identify the cell containing the value to look up, the first column in the lookup table, and the column containing the answer you want. VLOOKUP searches only the first column of its table range and returns a value from a column to the right. For example, if product codes are in column H, product names are in I, and prices are in J, a lookup for a price should use H:J as its table range. Make sure the lookup values and table values use compatible data types; a number stored as text may not match a real number.
Enter a VLOOKUP formula. With product codes in A2:A20, the lookup table in H2:J100, and prices in the third column of that table, enter this in B2: =VLOOKUP(A2,H2:J100,3,FALSE). The final argument, FALSE, requests an exact match. In this layout, if A2 contains P-104 and that code is on row 7 of the lookup table with a price of 12.5 in column J, the formula returns 12.5. The number 3 means the third column within H:J, not worksheet column C.
Lock the lookup table range. Edit the formula and change H2:J100 to $H$2:$J$100. The dollar signs fix both the column letters and row numbers when the formula is copied. With product codes in A2:A20 and the lookup table in H2:J100, the finished formula in B2 is =VLOOKUP(A2,$H$2:$J$100,3,FALSE). A2 remains relative so it can become A3, A4, and so on as you fill down; the table range stays put.
Use F4 to add the dollar signs if you prefer. While editing the formula, click or place the insertion point in the H2:J100 reference, then press F4. Excel cycles through reference styles. Press until the reference reads $H$2:$J$100. On some Windows laptops, you may need Fn+F4 because the function keys also control hardware features. In Excel for Mac, F4 may work, or you may need Fn+F4 depending on the keyboard and system settings. You can always type the dollar signs directly. In Excel for the web, edit the formula and type them if the shortcut is unavailable or behaves differently.
Keep the lookup value relative. Do not lock A2 if each copied row should use its own code. A formula copied from B2 to B3 should refer to A3 while still using $H$2:$J$100. If you specifically need every formula to look up the same cell, then lock that reference too—for example, change A2 to $A$2. Decide which references should move and which should stay fixed before copying.
Copy the formula to the other rows. Select B2 and drag its fill handle, the small square at the lower-right corner of the selection, down the required rows. You can also copy the cell, select the destination range, and paste. In the formula bar or a lower-row cell, confirm that the lookup value changed to the appropriate row but the table reference did not. With the layout above, the formula in B3 should use A3 and still use $H$2:$J$100.
Check whether you want a working formula or a fixed answer. An absolute reference does not freeze the result; it only keeps the selected reference from shifting when copied. The result can still change if the lookup value or source table changes. If you want the displayed answers to stop updating, first check that the formulas return the correct results, then select and copy the result cells.
Replace formulas with values when you need static results. Select the copied cells, then use Home > Paste > Paste Values. You can also right-click the destination or selection and choose the values paste option if it appears in the context menu. The cells now contain the answers rather than VLOOKUP formulas, so changes to the lookup value or source table will not update them. Test one or two cells by selecting them and checking the formula bar before replacing a large range.
Preserve a working copy when the formulas may be needed later. Before pasting values over formulas, duplicate the worksheet or save a separate workbook copy. Another option is to copy the formula results to a new range and paste values there, leaving the original formula cells intact. This is useful for reports, exports, or snapshots that should not change when the underlying data is refreshed.

Common Mistakes

Locking the wrong part of the formula. If you make A2 absolute but leave H2:J100 relative, copying down repeats the same lookup value while the table range shifts. Usually, lock the table range and leave the row-specific lookup cell relative.
Assuming an absolute reference freezes the answer. Dollar signs do not turn a formula into a fixed value. They only control how a reference adjusts when copied. Paste Values is the relevant method when you want to remove the calculation.
Using the wrong column number. VLOOKUP counts from the left edge of the selected table range. For H:J, H is 1, I is 2, and J is 3. If the number is larger than the range width, Excel returns a #REF! error.
Leaving off FALSE for an exact lookup. If the last argument is omitted or TRUE is used, VLOOKUP can perform an approximate match and may return an unexpected result, particularly if the first table column is not sorted appropriately. Use FALSE for exact matches such as product codes, names, and IDs.
Expecting VLOOKUP to search in either direction. The lookup column must be the first column of the selected range, and the returned column must be to its right. If the data is arranged the other way around, rearrange the range or choose a different lookup approach.
Overwriting formulas without a backup. Pasting values is intentionally destructive to the formulas in the selected cells. Ctrl+Z can undo the action immediately, but it is safer to preserve a worksheet or file copy before converting many cells.

Tips

Use exact matches for identifiers. FALSE is a good default for codes, account numbers, and other values that should match exactly. Approximate matching is intended for specific ordered lookup tasks, such as finding a bracket, and requires care with sorting and match behavior.
Keep the lookup table range no larger than necessary. A range that includes headers or unrelated columns can make the column index confusing. Start at the first row of actual lookup data and include only the columns VLOOKUP needs.
Use a named range if several formulas share the same table. Select the lookup table and create a name through Formulas > Define Name. A name can make formulas easier to read, but absolute cell references are often the simplest choice when you are learning or maintaining a small sheet.
Consider an Excel Table for expanding data. If the source data grows, converting it to a table with Insert > Table can make the source easier to manage. Excel tables can expand as rows are added, but check how your formulas are written and confirm the lookup column remains the first column of the VLOOKUP table reference.
Verify the result before filling hundreds of rows. Test a code that you know exists and one that should not match. Confirm the returned column and exact-match setting are correct, then fill down. This catches a wrong column index or shifted range before it spreads.
Distinguish a frozen result from a protected cell. Paste Values removes the formula, but it does not stop someone from editing the cell. Worksheet protection is a separate feature, and it does not make a VLOOKUP result static.

Troubleshooting

The copied formula returns #N/A. Check that the lookup value exists in the first column of the table range. Compare the actual values for extra spaces, different punctuation, or a number stored as text in one place and as a number in another. Also confirm the table range still includes the matching row. An absolute reference prevents copying from shifting the range, but it cannot correct mismatched data.

The result changes to the wrong value after filling down. Select a formula in a lower row and inspect its references. The lookup cell should normally move down one row, while the table range should retain dollar signs on both endpoints. If the lookup cell stayed the same, remove its dollar signs where appropriate; if the table moved, make its reference absolute.

Excel displays #REF!. Check the third argument, which is the return-column index counted from the first column in the table range. It must be at least 1 and no greater than the number of columns in that range. For a range H:J, use 1, 2, or 3—not the worksheet column number J.

The formula appears as text instead of calculating. Make sure the cell is not formatted as Text. Change its format to General, then edit the cell and press Enter so Excel reevaluates the formula. Also check that the formula starts with an equals sign and that Show Formulas has not been turned on from the Formulas tab.

Paste Values is unavailable or the result is not what you expected. Copy the formula cells first, then select the intended destination and use Home > Paste > Paste Values. If you are replacing the original cells, confirm the selection before pasting. In Excel for the web or on Mac, the exact context-menu layout can differ, so use the ribbon’s Paste Values option where available. If you pasted over formulas by mistake, use Undo immediately or restore the saved copy.

Frequently Asked Questions

Does freeze VLOOKUP mean locking the formula?
It can. In common use, it often means locking the lookup table range with dollar signs so the range stays the same when copied. It can also mean turning the formula results into values. Choose based on whether you need the formula to keep calculating.

How do I freeze only the VLOOKUP table range?
Put dollar signs before both columns and rows in the range, such as $H$2:$J$100. Keep the lookup cell relative if each row should search for its own value. You can type the signs or use F4 while editing the reference.

Will a frozen VLOOKUP result update if the source data changes?
If you mean an absolute-reference formula, yes. It still recalculates when the lookup value or source data changes. If you copied the result and used Paste Values, it will no longer update from those source changes.

Can I freeze just the answer without changing the source table?
Yes. Copy the cells containing the VLOOKUP results and paste values over those cells or into another range. Only the selected cells are changed; the source table remains in place.

Does pressing F4 freeze the whole VLOOKUP formula?
No. F4 cycles the reference style for the cell reference currently selected or under the insertion point, such as A2 or H2:J100. It does not convert a formula to a fixed value. Use Paste Values for that.

Why does my VLOOKUP still change after I add dollar signs?
Dollar signs only prevent references from adjusting when a formula is copied or filled. The formula still recalculates if its inputs or source data change. To preserve the current displayed answer, replace the formula with its value.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Errors & fixes

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.