Why are there $ signs in some vlookup formulas when the data does not include currency values?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Why $ signs in vlookup formulas?
0
Discussion
Please don't forget to select the answer that worked for you! Just click the Select Answer button at the bottom of the desired answer.
don (rep: 1989) Jan 17, '18 at 9:18 am
Answers
0
The $ sign in formulas has nothing to do with currencies. Instead it is an indicator as to how to interpret the letter or number it precedes. This indicator is without effect until the formula is copied to another location. In that context its necessity becomes apparent immediately.
Excel must differentiate between absolute and relative references. An absolute reference is one where the written cell address is the address of the cell referred to. A relative address counts a fixed number of rows and/o columns from the location where the formula resides to the cell that is referenced.
Assume, for example, that the formula =A1 resides in cell C3. A1 is located 2 rows up and 2 columns left of C3. Therefore, if the address is to be understood relative and you copy it to D4, 2 up and 2 left would result in B2, and Excel will actually make that change in the formula automatically as part of the copying process.
On the other hand, if the reference in C3 is =$A$1, indicating an absolute address, that reference will not change wherever you copy the formula.
Making one of the coordinates absolut and the other relative, such as =$A1 is very common practice. As this formula is copied to the right or left it continues to point to column A but when copied down it will change the referenced row number automatically to keep it equidistant relative to the cell it was originally written to.
Find another way of explaining this concept here.