By Sally Gerlach, Assistant Director of Institutional Effectiveness, Hamline University
VLOOKUP has been an extremely helpful tool to add variables from one dataset to another in Excel. Sometimes, however, you may want to merge in two or more variables from a different dataset. You can either use VLOOKUP and change the col_index_num manually across all the columns of data you want to add (e.g. from 2 → 3 → 4 → 5 → 6 → etc.), or you can use the COLUMN function embedded into the VLOOKUP and drag/copy your formula across all the cells you want to fill.
If you have not used VLOOKUP before, there is a great eAIR Tech Tip by Dale Amburgey titled Using the VLOOKUP Function in Microsoft Excel that should be referenced first, as it does a great job of going over the VLOOKUP formula.
NOTE: One difference in Ambugey’s example is that the table_array was locked with the $ symbol, and in this example the data to be added will be defined as a named range. The benefit of doing a named range is that it locks in your table data without having to remember to use the $ in the right spots. The Named Range Advanced Tip section at the end of this tip provides more detailed instructions on creating and managing named ranges.
Tables Referenced:
Main Dataset:
Data to be Added Example: (named ProgData)
Basic VLOOKUP formula (using a named range in the current workbook:
=VLOOKUP(A2, ProgData, 2, FALSE)
A2: lookup_value
The matching variable the formula is searching for in the data to be added, ID 3699.
It must be in the leftmost column of data table that is being added.
ProgData: table_array
The data table is being searched and added from; here it is named ProgData.
If you were to use a named range of data on a different workbook, you would need the file extension inside single quotes and an ! before the named range (=VLOOKUP(A2, 'Data for Tech Tip-pt 2.xlsx'!ProgData, 2, FALSE).
2: col_index_num
The column number of the field you want to add. You must enter this number manually, adding data from the column 2 (Degree).
This is the item we are going to use the COLUMN function on so we don’t have to change it manually.
FALSE: range_lookup
The type of match to find.
False means it searches for an exact match.
True means it searches for the closest match (if in ascending order).
Using the COLUMN function
So we don’t have to manually change the 2 to a 3 to a 4, and so on in the col_index_num of our formula, the COLUMN function can be used:
=VLOOKUP($A2, ProgData, COLUMN(B1), FALSE)
The COLUMN function returns the number of that column, going from left to right. For instance:
Using this function allows you to copy the VLOOKUP formula across multiple columns while automatically shifting the column of reference by one. Your first cell will show COLUMN(B1), but the next column will adjust to COLUMN(C1) automatically. Don’t worry about the row number shifting as you copy the formula down the column, as it doesn’t matter.
Because we are going to copy and paste this formula across multiple columns, we need to lock the column of the variable we are matching on (column A - ID) so that it does not shift over a column. To do this, either enter a $ before the column letter or use the F4 key to cycle through until the $ is before the column letter (Note: this does not always work with every computer/operating system).
Drag Formulas shortcut: If you move your cursor over to the bottom right-hand corner of the cell you are going to copy, the cursor will change from a clear plus sign to a solid one. Once it changes to the solid plus sign, you can hold the right button on your mouse and drag the selecting box (bold lined box) to the cell you want. The formula will automatically fill in and change, as it would with the copy and paste method (notice below that the last cell is referencing G1 in column N).
For the rest of your data, you can either copy and paste or drag the formula data down that you just added.
Another shortcut: You can highlight the portion of row data you want to copy down. When your cursor changes to the solid plus sign, double-click and it will fill all the way down, as long as there are no blanks in the data for the column to the right.
Extra Tips:
Copy over the header fields into your dataset so that you know how far over you need to take your formula.
Copy and paste-special-as-values over the column(s) that you did a VLOOKUP on to help avoid any possible data changes later on.
Dates may have to be adjusted as they sometimes come over as a number. Just change the format of the cell to Date.
If the formula returns a #N/A, there could be two possibilities: 1) it could just be that there is not a match – easy to check, 2) there is an error with your formula. Sometimes this may be easy to fix, but other times not as easy.
If you are going to run similar data in the future and think you might need to use the workbook as a reference, you can insert a row above your column titles and copy and paste the VLOOKUP formula into the cells above so that you will have reference to the VLOOKUP(s) and from what it was adding. You can change the format of the cell to text so you can see the formula and not the answer.
VLOOKUP can also be used to find a specific data point. Say you want to know what degree Mila Howard (3699) was pursuing: =VLOOKUP(3699, ProgData, 2, false) = Bachelor of Arts
Named Range Advanced Tip:
To create a named range of your data, highlight the data you want to add and type its name in the box on the left-hand side of your worksheet, above column A (no spaces, and don’t use an actual cell name (e.g. CH5). You must hit the ENTER key in order for the named range to be applied. In this case it is named ProgData. You can check to make sure the named range was applied by typing the name in this same box. It will highlight the cells that have been selected. To view or manage the named ranges of a workbook, press CTRL + F3 at the same time, which can be very handy when using many VLOOKUPs in your data.