Excel- Mastering VLOOKUP

The Vlookup is one of the most popular functions in Excel but, until you fully understand it, it may initially appear complicated. There are also some users have heard of the Vlookup function in Excel, but don't really understand what it does.

Therefore this tutorial begins by answering the common question, "What is Vlookup in Excel?" This is followed by an explanation of how to use the Vlookup function in the two situations when either an exact match, or a closest match, is required. The tutorial also provides practial Vlookup examples for each of these cases.

If your Vlookup is not working as expected, you may want to skip straight to the section on Vlookup errors. This section will take you through a logical set of steps that will help you to find the cause of your Vlookup error, and identify a solution.

Many users have heard of the Excel Vlookup function but are not clear about what it does. Therefore, this page aims to answer the common question "What is Vlookup in Excel?"

Vlookup is one of Excel's built-in functions. The function is used when you want to find a value in the left-hand column of a vertical array of data, and return the corresponding value from another column in the same array.

This best explained through the following example.

Vlookup Example

Imagine that your company keeps a spreadsheet of employee hourly rates of pay (see 'Hourly Pay' spreadsheet below). At the end of each month, the sales team manager sends you a list of hours worked by each of his staff during the month (see 'Sales Team Hours' spreadsheet below). It is your job to complete the 'Sales Team Hours' spreadsheet, to show the pay owed to each member of the sales team.

Spreadsheets Used in Excel Vlookup Exact Match Example

In order to calculate the pay owed to each sales team member, you first need to look up the hourly rate of pay for each person and insert this into column C of the 'Sales Team Hours' spreadsheet. This can be done using the Excel Vlookup function.

As illustrated below, if the Vlookup function is entered into cell C2 of the 'Sales Team Hours' spreadsheet, this can look up the name "Benson, Paul" in column A of the 'Hourly Pay' spreadsheet and return the corresponding rate of pay from column B. Therefore, in this example, the Vlookup function returns the value $32.00.

Vlookup Illustration

The full syntax of the Vlookup formula in the above example is not shown as, for now, we simply wish to explain what the Vlookup function does. The syntax of the Vlookup function is explained in a later stage of this tutorial.

However, for those who want to skip ahead, the above Vlookup is described in part 3 of this tutorial: Vlookup Example of Finding an Exact Match.

Exact Match vs. Closest Match

In the above example the Vlookup is used to find an exact match to the lookup value (which is the text string "Benson, Paul").

The Vlookup function can also be used to return a closest match to the lookup value if an exact match is not found. An example of this is given in part 4 of this tutorial: Vlookup Example of Finding the Closest Match.

If you are still unclear about the purpose of hte Vlookup function, a further explanation is provided in a short video on the Microsoft Office website.

Vlookup Syntax The syntax for the Excel Vlookup function is:

VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

where the function arguments are:

lookup_value - The value that you want to search for.

table_array - The array of data that is to be searched for the lookup_value. The Vlookup function searches in the left-most column of this array.

col_index_num - An integer, specifying the column number of the supplied table_array, that you want to return a value from.

[range_lookup] - An optional logical argument, which describes what the function should return in the event that it does not find an exact match to the lookup_value.

The [range_lookup] can be set to TRUE or FALSE, meaning:

TRUE - Find the closest match below the lookup_value if the exact value is not found.

Note: if this option is used, the left-hand column of the table_array must be in ascending order.

FALSE - Find an exact match to the lookup_value - if an exact is not found, the function returns an error.

If the [range_lookup] value is omitted, it takes the default value of TRUE.

Vlookup Rules It is important that you understand the following rules when using the Vlookup function in Excel:

The data in the supplied table_array must be organised in columns.

The function sees upper case and lower case text as being equal (i.e. a lookup of the string "TEXT" will match the string "text").

The Vlookup function does not recognise numbers and text representations of numbers as being equal. Therefore, if your lookup_value is equal to the text string "10", but the values in the table_array are numeric, the function will fail to find a match.

The Vlookup function considers all characters (including spaces) when searching for a match. Therefore, the two text strings, "text" (no spaces) and "text " (with trailing spaces) are not an exact match.

Rules for the Vlookup Using an Exact Match

If the [range_lookup] argument is set to FALSE, the Vlookup searches for an exact match to the lookup_value. If an exact match is not found, the function returns the #N/A error.

In this case, the following rules apply:

If the lookup_value is duplicated in the left-hand column of the table_array, the function uses the first match that it encounters.

If the lookup_value is a text string, you can use wildcard characters, where the character ? matches any single character and the asterix, * matches any set of characters. For example, a lookup_value "t*" would match any text string beginning with the character "t".

Rules for the Vlookup Using the Closest Match

If the [range_lookup] argument is set to TRUE (or omitted), the lookup_value is matched to the closest value below or equal to this value.

Therefore, if the [range_lookup] argument is TRUE, the following rules apply:

The left hand column of the supplied table_array must be in ascending order. If it isn't, the function may return unpredictable results. If the lookup_value is smaller than all of the values in the left-hand column of the table_array, the Vlookup function returns the #N/A error.