VLOOKUP COLUMN Function and Named Ranges

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:

a1vlookupcolumn.PNG 

Data to be Added Example: (named ProgData)

a2vlookupcolumn.PNG
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)

3.PNG
The COLUMN function returns the number of that column, going from left to right. For instance:

  • =COLUMN(B1) returns the reference of 2
  • =COLUMN(C1) returns the reference of 3

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.

  • =COLUMN(B1) returns the reference of 2
  • =COLUMN(B4) returns the reference of 2

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).

  • $A$2 – locks onto A2 only
  • A$2 – locks onto row 2
  • $A2 – locks onto column A

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).

a4vlookupcolumn.PNG
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.

a5vlookupcolumn.PNG

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 1
 
Marlene posted on 6/24/2016 10:27 AM
Great tip!

​