Using the VLOOKUP Function

Understanding VLOOKUP and HLOOKUP

The VLOOKUP function will look in the leftmost column of a table for a value you specify. When it finds the value you specify, it will return a value that is located in the same row, a specified number of columns into the table. It is called VLOOKUP because it looks vertically down a column for a match, and then retrieves data from somewhere across that row.

HLOOKUP is similar, but it will look horizontally across the upper row of your table, and then retrieve data from somewhere below in the column. Since Excel is designed with more cells in the vertical direction than in the horizontal direction, and because vertical table design is more intuitive for most people, VLOOKUP is generally used more often than HLOOKUP.

Using VLOOKUP to Find Data

The best way to learn how lookup functions work is to look at an example. Here we have a table of ticket prices for flights to different countries. To simplify matters, the data range for the table has been given a defined name (“price”) that can be used in functions and formulas.

The arguments for the lookup function are:

VLOOKUP (value to match, lookup table name or range, number of the column in the table containing the relevant data, true or false). 

If we activate cell F1 and enter =VLOOKUP ("England", price,2) into the formula bar, F1 will show the value 550: 

• The lookup function looked vertically down the leftmost column of the lookup table (price) until it found a match for the text string “England.” The function then returned the value that is in the second (2) column of the table, in the row where the match was found. You should notice, that England, price, and 2 are the exact arguments used in the function.
• For this example, the true or false argument was left out. The relevance of the true or false argument in the VLOOKUP function will be discussed shortly.
• To use the VLOOKUP function correctly, you need to have your spreadsheet data laid out properly in table form with at least two columns. The first column in the table will contain the keys (identifiers that the VLOOKUP function will examine for a match). In the example just shown, the keys are the names of the countries. This first column can be referred to as the lookup column.
• The other columns in your table will contain data that corresponds to the column of keys. Your table can be several columns wide, and you can specify which column VLOOKUP will retrieve data from by putting a number corresponding to the given column in the function. 
• In the previous example, we wanted VLOOKUP to return the ticket price, so we used the number “2” (for the second column) as an argument in the function. If your table has 10 columns and you want to return data from the ninth column, you would use 9 as an argument.
• You do not have to use text values (like the country names used here) in your lookup column. If it is more appropriate, numbers or dates will serve just as well.

If you want some help when you are using VLOOKUP, use the Insert Function dialog:

You will find the VLOOKUP function in the Lookup & Reference category. If you click OK in the Insert Function dialog, you will see the helpful Function Arguments box:

Simply enter the function arguments in the fields provided.

How to Find an Exact Match with VLOOKUP

As mentioned briefly before, the VLOOKUP function can have a final argument of either TRUE or FALSE. If you specify this final argument as FALSE, VLOOKUP will search for an exact match in the lookup column. If it cannot find an exact match, no data will be returned.

In the following example, the function =VLOOKUP ("jpn",price,2,FALSE) has been entered in cell C1. This means that VLOOKUP will search the first column of the table for an exact match of “jpn,” and if it is found, it will return the corresponding value in column 2.

Because there is no country that exactly matches “jpn,” no value will be returned to cell D1. If you change the function so that the first argument is “Japan,” an exact match will be found. The value 925 is returned to cell C1.

If there are two or more exact matches, the data corresponding to the first matching value found in the lookup column will be returned.

Finding an Approximate Match with VLOOKUP

If the final argument in your VLOOKUP function is TRUE, VLOOKUP will search the lookup column for an approximate match to the search value you enter in the function. If there is an exact match to your search value in the lookup column, the corresponding item from that row will be returned. However, if no exact match is found, a value corresponding to what Excel deems is an approximate match will be returned.

• For example, if you enter “jpn” as the search value for the table of ticket prices, the value associated with the country Japan (925) will be returned. “Japan” is the closest match to “jpn” that Excel can find in the lookup column.
  
• Similarly, if you enter “Ity” as the search value for the function, the airfare corresponding to Italy will be returned:
  

• This feature can be useful if you have a lengthy lookup column and you aren’t quite sure how to spell what you are looking for; however, there are no guarantees when looking for an approximate match.
• If you omit the final argument in your VLOOKUP function, it will default to TRUE, and search for a closest (approximate) value if an exact match cannot be found. If you use the TRUE argument (intentionally or by default), the values in the lookup column should be sorted in ascending order. 
• Otherwise, you may get unexpected results. If you are using text values in your lookup column, avoid using leading or trailing spaces, as this may also produce unexpected results.
• If you use FALSE as the final argument to your function (for an exact match), the lookup column does not have to be sorted.

Using VLOOKUP as an Array Formula

You have just seen how to use VLOOKUP to find a data value in a row that corresponds to the presence of a search value in a lookup column. Now that you know how to return a single data value with VLOOKUP, the question becomes, how do you use VLOOKUP to find multiple values?


Study the following worksheet. It is an extension of the previous examples in this lesson.

• The range of data in the Air Fare table is called “ticketData.” This range contains information regarding the availability of first and second class seats. If there are seats available, the value is “yes,” and if there are no seats available, the value is “no.”
• Suppose you want to have the VLOOKUP function return the data for the ticket price, and for the seats that are available. You can perform this task by using VLOOKUP as an array formula

Here is a VLOOKUP function that will retrieve three data items:

=VLOOKUP ("Japan",ticketData,{2,3,4})

• As before, the first argument is the search value: “Japan.” The next argument is the range of data to be searched. (The range has been named “ticketData” to make things easier.)
• The next argument is supposed to identify the column from which VLOOKUP retrieves the data if a match is found. This time, a set of three numbers has been given as the argument “{2,3,4}” rather than just one number. Our intention is to have VLOOKUP retrieve multiple values from the table of data.
• To make this function work, we must first select a set of three cells to receive the data. Next, we will add the VLOOKUP function to the formula bar:
  

• To make the function work as intended, we will now enter it as an array formula (by pressing Ctrl + Shift + Enter). We know the function has been successfully entered as an array formula by the curly braces that appear around it.
• The VLOOKUP function now returns multiple data items from the table based on a match with the search value “Japan:”