How to prevent vlookup table from moving, when filling cells in any direction that contains a vlookup equation?

Ive made a vlookup function, utilising 2 drop down cells, but when I copy it using the “fill down” or “fill right” the “table” moves, so I have to manually adjust it every time so that the vlookup provides the correct results.

Is it possible to prevent the vlookup table from moving when I use the fill function to copy the vlookup expression when it is pasted to new cells?

If so, how?

You need to use absolute addressing for the range.

Prefix letter and number in the range address with a dollar sign.

E.g. A3:F100 should be $A$3:$F$100.

You can also use a named range instead of explicit addresssing. Named ranges will be “absolute” in this respect. A shortcut to assign name to a range: Select the range, and in the address field (in the toolbar, far left from the formula field) you can type a name. Take note of naming rules given in the documentation (linked above).

Some advantages to using named ranges:

  • The formula is easier for us humans to read as long as you use sensible names. =AVERAGE(X25:Z223) requires that you know what the X25:Z223 range holds, but =AVERAGE(Payments) is rather obvious.
  • If you adjust the range size, all references to it by name will update to reflect the change. No need to manually update every reference.

ty, that fixed it.