What does it exactly do? Well, it takes a defined value from a dataset and searches for it in another dataset utilizing the value as the key between both. In MS Access this would be a 1:n relationship. Sounds complicated? Then let's head over to the example to make it more understandable. :-) Imagine we have two tables:
Clients
| A | B | C | D | |
| 1 | Client | City | State | Account Mgr. |
| 2 | ABC Articles Corp. | New York | NY | |
| 3 | Example Corp. | Los Angeles | CA | |
| 4 | Sample Corp. | Miami | FL | |
| 5 | Inservo Internet Marketing | Dallas | TX | |
| 6 | Microsoft Corp. | Seattle | WA |
Account Managers
| A | B | |
| 1 | Managed State | Account Manager |
| 2 | NY | Jack Miller |
| 3 | CA | Peter Johnson |
| 4 | FL | Adele Williamson |
| 5 | TX | Abraham Jackson |
| 6 | WA | George Peterson |
Display Excel tables online >> Excel Jeanie HTML 4
We now want to place the correct account manager from the corresponding table into the D column. The formula we will use for the first row of values (ABC Articles Corp. located in New York, NY) looks like this:
=VLOOKUP(C2;'Account Managers'!A1:B6;2;FALSE)
By the way, I always suggest to use "input wizard" by clicking the small insert function icon left of the formula bar: 
You will find the lookup functions in the reference and lookup section of the insert function dialog box.
So let's start with the analysis of the parts it consists of. The values of our example are shown in the braces:
The function name "VLOOKUP"
The functions name "VLOOKUP" tells MSXL that we are working on vertical oriented data. Its twin is the HLOOKUP function which submits the information that we are working on hroizontal oriented data.
The Lookup_value (C2) The lookup value tells MSXL what our identifier for both datasets is, asking the question "What is the same value in both tables?" In our case it's the state abbreviation.
The Table_array ('AccountManages'!A1:B6) The table array tells MSXL where to look for the data. It defines our target dataset we get our information from. In our example it is a table on another sheet called "Account Managers" holding the state the account manager is responsible for and his or her name.
The Col_index_num (2) The column index number is the target information column MSXL should return after finding a match. It is shown as a number where the counting starts with "1" from the left. In our example the column no. 1 holds the state information which should not be returned as we already have it (as an identifier) in our main table and column no. 2 holds the requested data.
The Range_lookup (FALSE) The range lookup is a logical input which can hold two values: TRUE and FALSE. If the entry here is TRUE, the function looks for the closest match if it cannot find an exact one. This is very nice for working with ranges e. g. scale pricing etc. We will have a post on VLOOKUP with the range lookup holding the value "TRUE" in tomorrows post. In todays example we chose FALSE as we wanted to have an exact match, meaning if we would have entered a state not available in our target table - e. g. CO instead of CA in line 3 - Excel would return the #N/A error message. Our table (showing the function arguments for cell D2 in this example should look like this:
For the purpose of this example the datasets are very small but the VLOOKUP function (like its brother / sister the HLOOKUP function) comes in very useful when it comes to larger datasets. As mentioned before tomorrow we will have a look on using a "TRUE" range lookup which takes us to the next step with the LOOKUP functions. Do you have any suggestions for the usage of this function? Tell me in the comments section of this post.
Discuss this blog post in the forums (click the blue button)
