VLOOKUP issue, need absolute references

**RichardLP** · 11-05-2021, 09:33 AM

Greetings,

This issue occurs from time to time, and I have not pinpointed what causes it.

Typically, when I write a VLOOKUP formula and copy down, there is no issue. It automatically applies absolute reference to the range. What happens on the times it fails is that my formula does not automatically set absolute reference. I have to manually add the $-sign to the range in the formula, then copy down.

Thank you for support.

**MarvinP** · 11-05-2021, 09:52 AM

Hi Richard,

You need to learn about the F4 key to cycle through the Absolute vs Relative references in a formula.
https://edu.gcfglobal.org/en/excel-t...-the-f4-key/1/

**TMS** · 11-05-2021, 09:53 AM

I have never known VLOOKUP, or any other function, to automatically set absolute references.

**MrShorty** · 11-05-2021, 11:11 AM

I agree with TMS. Excel almost always defaults to relative references, and it is up to the programmer/user to specify absolute references.

The major exception to this is that Excel will default to absolute references when you click on a cell/range in an external workbook while building formulas. If you are seeing mixed behavior, I wonder if you are frequently using external references for your lookup tables. In that case, you might be seeing that Excel's default is different for external references.