Vlookup (or ‘Vertical Lookup’) is an Excel function that combines 2 sets of data based on a common ‘key’ column. The function is basically being asked to look at a shared value (for example, client_id), and then give another value or set of values based on that original value. In the example spreadsheet, ‘V Lookup’, we can see that there are 2 tabs, CODE and CITY. CITY has a Client ID as well as a city name and province, and CODE has a Client
ID as well as a letter code and a city name. If you select any of the cells in the City column in the city spreadsheet you’ll see a City name, which was manually typed into that cell.
Here’s the interesting part: if you select any of the cells in the City or Province column in the CODE tab you’ll notice that the city/province name is actually the VLOOKUP formula: =VLOOKUP(lookup,sheet!range,column,match). This means that we’re not actually seeing a value that was typed-into the cell, but that the VLOOKUP formula is providing that value from another sheet based on the reference match provided.
Let’s take a closer look at the first few rows of output of CODE:
| Client_ID | Client Code | City | Province |
|---|---|---|---|
| 1 | A | Toronto | ON |
| 2 | B | Ottawa | ON |
When we select the first two columns we can see that ‘1’/’2’ and ‘A’/’B’ are values that were typed in, but selecting a cell in the City or Province columns respectively give us
=VLOOKUP(A2,CITY!$A$2:$C$14,2,FALSE)
or
=VLOOKUP(A2,CITY!$A$2:$C$14,3,FALSE)
These are the VLOOKUP formulas that are looking at the values in the CITY tab and returning them to our cells in the CODE tab. To break it-down, let’s take a look at the components one-by-one.
This is what the base formula looks like
=VLOOKUP(lookup,sheet!range,column,match)
with VLOOKUP being the formula name. If we examine each of the components individually by function, we can see that the function above is actually
DO A COLUMN LOOKUP (TAKE THIS VALUE FROM THE PRESENT SHEET, MATCH IT TO THE SAME VALUE FROM THIS RANGE ON THIS OTHER SHEET, RETURN THIS VALUE FROM THE OTHER SHEET, MATCH THE VALUES EXACTLY).
1) lookup
=VLOOKUP(A2,CITY!$A$2:$C$14,3,FALSE)
The lookup (or cell from the CODE tab) is simply the value you want to match on. In this case, the formula is saying ‘Take the value you see here in cell A2 and give me back the values from some other columns in the same row
where that value matches’.
2) sheet!range
=VLOOKUP(A2,CITY!$A$2:$C$14,3,FALSE)
The sheet!range tells the formula where it is to look for the matching value. We have two parts here, CITY! and $A$2:$C$14. As you may have guessed, ‘CITY!’ is the ‘sheet!’ part and tells Excel that the values we’re looking for
aren’t on this sheet (CODE), they’re actually on another sheet in the same workbook (CITY). The ‘range’ part, or ‘$A$2:$C$14’ in our example, tells the formula that once it’s on the CITY sheet, that it should look for the matching
value in the cell range A2:C14, which is basically the section of the CITY tab that has data in it. The easiest way to select the range is to start entering the VLOOKUP formula and then when entering the range, navigate to the
selection you want and just drag the mouse across the desired range.
The ‘$’ sign simply tells excel this is an absolute lookup, not a relative lookup. That way if we’re auto-filling the formula across many rows and columns in a sheet the range will stay the same. Excel has a handy trick of auto-incrementing ranges, but if we want the range to stay the same we need to ‘show it the money’ (or dollar sign, as the case may be). If we didn’t add the ‘$’, we would have the range increase every time we dragged the formula
down, and the range on the CITY tab would shift downwards to include blank values. For example, A2:C13 would then go to A3:C14 and we would lose the ability to lookup the values from A2:C2 (which contains data) and would replace them with the lookup values from A14:C14 (which is blank).
3) column
=VLOOKUP(A2,CITY!$A$2:$C$14,3,FALSE)
The column variable tells the formula what value to return from the lookup range specified just before. In our case, the formula is going to take the value in A2 on this, the CODE tab, match it to a value from a range on the CITY tab, and then return the value from the specified column on the CITY tab, which is column 3 or ‘Province’ in this case, to the CODE tab. The formula is returned to the cell which contains the formula.
4) match
=VLOOKUP(A2,CITY!$A$2:$C$14,3,FALSE)
The match variable tells the formula whether to get an approximate (TRUE) or exact (FALSE) value. Since we are setting the variable to FALSE, only those Client ID’s that exactly match the search are going to be returned.
Returning multiple columns from a single VLOOKUP search
Looking at the row 2 entries for columns C and D on the CODE tab, we can see that the formulas are defined as:
=VLOOKUP(A2,CITY!$A$2:$C$14,2,FALSE) for column C
and
=VLOOKUP(A2,CITY!$A$2:$C$14,3,FALSE) for column D
The difference (2 vs. 3) refers to which value from the CITY tab is being returned. In the case of our CODE tab, our C column is getting back the value from the second column of CITY (hence the value ‘2’), and our D column is getting back the value from the third column of CITY (hence the value ‘3’).
