Vlookups are among the most useful formulas.in excel because they allow you to find data within a table array quickly and easily. However, one of the most common problems that people have with vlookups is that they cannot be copied to other cells without changing the cell references of the table array. So how do you stop that happening?
All formula is within excel, by default change, when the formula is copied and moved to another cell location and vlookups are no exception to this. However, it is fairly easy to stop the cell references changing by placing dollar signs within the formula, An example of this is shown below;
An example of a vlookup formula prior to the addition of $dollar signs:
=vlookup(A2, G1:Z100, 2, FALSE)
After the addition of dollar signs: =vlookup(A2, $G$1:$Z$100, 2, FALSE)
What Do The Dollar Signs Do?
The dollar signs in any excel formula are designed to fix the position of cell reference even if it is copied to another location. However, because there are two elements within any cell address to fix their position you need to Include two dollar signs in every cell reference, one before the letter of the column and the other before the letter of the cell.
However, there will be occasions where you only want to fix one of these elements. An example of this may be when you want to copy the vlookup into the next column and you still want a reference the same column. In this case the formula would be as follows:
=vlookup(SA2, $G$1:$Z$100, 2, FALSE)
In this example it allows the formula to be moved left or right without changing column locations, however, if you want to copy the formula down this would then this will ensure that the lookup value is still assigned correctly to the next row down.
In cell b2 the formula would be: =vlookup(SA2, $G$1:$Z$100, 2, FALSE)
When it is copied down to cell b3 the formula would become:
=vlookup(SA3, $G$1:$Z$100, 2, FALSE)
To see all this information in a video along with some tips on how to make the process faster check out a video below or read the tips at the bottom of the article.
Tip 1 – Entering the dollar signs faster
The Dollar signs can be applied to the formula quickly and easily by placing the cursor on the relevant cell reference and pushing F4. If you push F4 multiple times it will apply dollar signs to the different cell references until you find the right one that you want ie =F2, =$F$2, =$F2 or =F$2.
Tip 2 – Copying The Formula Faster In Columns
Once the vlookup formula has been completed it can be copied downward to length of the data by double clicking on the square in the bottom right hand corner of a cell where the formula is. In the screenshot below this is in cell B1. When your cursor is in the correct position to do this it will change to a plus sign.
Tip 3 – Reducing The Amount Of Data That You Need To Enter
In cases where you are taking more than one column of information from a table array in columns next to each other the fastest way to get the formula entered Is to create the first formula so that column of the lookup value is fixed ie =vlookup(SA3, $G$1:$Z$100, 2, FALSE). The formula can then be dragged into the adjacent columns and you only need to change the column reference number.
Tip 4 – How To Use A Vlookup To Enter Data Into A Form
One of the other very useful ways to use a vlookup is in a template as it can be used to collect data based on a user’s selection which then will allow the template to be filled quickly and easily. An example of this might be the creation of a costing template where the user can select the name of the product and have its code and price automatically brought into the sheet using a vlookup.
This can be done by creating a drop down list within a cell which will allow the user to select the product they want. The vlookup can be placed in a cell with a conditional formula to prevent the cell from looking up the value with will prevent an error from occurring. An example of a conditional formula would be;
=if(A3=””, “”, vlookup(SA3, $G$1:$Z$100, 2, FALSE))
Relevant Articles
How Long Does It Take To Learn Excel?