Introduction to VLOOKUP

Availability: All versions

The name VLOOKUP stands for vertical lookup. It looks for a value, within a table, and returns a value from the same row but from a different column in the table.

The VLOOKUP function always looks for a value in the first column of the table in which it is told to search.

There are many reasons VLOOKUP is used. The most common use is to look up values in another table to combine them into one main table. This table would then be used for the analysis such as a PivotTable.

Other reasons include to compare two lists for differences, automate data entry on a spreadsheet, and fetch values from a lookup table for use in calculations, for example, a lookup table of different tax rates by city.

VLOOKUP for an Exact Match

The most common form of VLOOKUP is to perform an exact match. This means that you are looking for a unique value such as a product code, booking reference, or an employee ID.

In this first example, we have a table named [tblTiers] that contains the prices for different membership tiers (Figure 7-1).

We have a table of customers and the membership tier they are enrolled in. We want to use VLOOKUP to return the price for each customer’s membership.

VLOOKUP for a Range Lookup

The second type of lookup that you can perform with VLOOKUP is a range lookup. With this type of lookup, you are looking within ranges of values. For example, these could be date or time ranges or other values such as exam scores, amounts spent, or some other performance value.

This type of lookup, although not as commonplace as an exact match lookup, can be very useful. Let’s see two examples.

In the first example, we have the lookup table shown in Figure 7-3. The first column contains a range of quantity values. We have a table of orders and want to look up the quantity ordered and return the earned discount.

When performing a range lookup (also known as an approximate match), it is essential that the first column of the lookup table is in ascending order.

VLOOKUP will match the value to the closest value less than the one searched for. So, for example, if we were looking for a quantity of 49 in the lookup table of Figure 7-3, the discount of 3% is returned, as 15 is the closest match less than 49.

This argument is named range lookup , as it is asking you if you are looking in ranges or not. In this example, the answer is true; yes, we are looking in ranges of values. Remember, we could also type 1, or omit a response, as TRUE is the default lookup type.

In the second example, the lookup table in Figure 7-5 contains date ranges in its first column. In the second column of the table is a tax rate that changes at specific dates. This table is named [tblRates].

In this formula, the range lookup argument is omitted. The default result of the range lookup argument is True, so this is fine.

Column Index Number Tricks

In the examples demonstrated so far, the lookup tables have been small, and the column index number has always been 2. When looking up data in larger tables, counting to find the correct column index number can be frustrating.

There are a few tricks that we can deploy to save having to count to find the required column index number. These tricks also work in making our VLOOKUP formulas more durable, as when typing the number directly into the formula, it can easily be broken by someone inserting or deleting a column.

Another mistake can be that a user did not count hidden columns when finding the column index number.

Trick 1: The COLUMN Function

Availability: All versions

The COLUMN function returns the column number of a given reference. This reference can be a cell, table column, or a named range. It returns the column number of the sheet, for example, column D is column number 4.

The COLUMN function only asks for the reference from which to return the column number. This is an optional argument. If omitted, the column number of cell where the formula resides is returned.

=COLUMN([reference])

This function is ideal for the task of preventing having to find the column number in the lookup table ourselves. We can just reference the column of the lookup table that we want, and COLUMN returns its index number.

In Figure 7-7, we have a table named [tblReps] containing data about our sales representatives. We will use a VLOOKUP function to return the data from the [Status] column to another table.

The following formula is used in Figure 7-8 to return the status data. A similar formula was also used in cell F2 to return the rep name.

=VLOOKUP(E2,tblReps,COLUMN(tblReps[Status]),0)

The COLUMN function is provided with the [Status] column of the table. This makes it very simple to provide a column number to VLOOKUP, as referencing columns in a table is fast and easy. This technique was covered in Chapter 4.

Remember, the column number returned is the column number of the worksheet, not the table.

As this lookup table (Figure 7-7) starts in column A, the worksheet column and the table column are the same number, so it is not a problem. However, let’s explore an alternative scenario.

In Figure 7-9, the [tblReps] table now begins in column C. So, the [Status] column is the fifth column of [tblReps], but the seventh column of the worksheet.

For the COLUMN function to work, we will need to subtract two from its result to account for columns A and B not being used by the table.

In the following formula, minus 2 is entered after the COLUMN function (Figure 7-10):

=VLOOKUP(E2,tblReps,COLUMN(tblReps[Status])-2,0)

If someone were to insert another column, or remove a column, before the start of the table, our formula would fail. The subtraction of 2 would no longer be relevant.

For a more robust version of this formula, we want to dynamically return the column number of the column directly before the table, 2 in the previous example. To do this, the COLUMN function will be used to return the sheet’s column number of the first column of the table. One will be subtracted from this to return the column number that precedes the start of the table.

The following formula is entered in cell G2 (Figure 7-11). The calculation to return the start column is enclosed in brackets to calculate first.

=VLOOKUP(E2,tblReps,COLUMN(tblReps[Status])-(COLUMN(tblReps[ID])-1),0)

Trick 2: The COLUMNS Function

Availability: All versions

The COLUMNS function returns the number of columns in a given reference or array. This could be a reference to a range of cells, a table, a named range, or an array formula.

The COLUMNS function only requires the array for which to return the total number of columns:

=COLUMNS(array)

This function is very useful when the column number you require is at the end or a specific number of columns from the end of a reference or array.

In Figure 7-12, we have a table named [tblMonthly] that contains monthly performance values for staff. We require a VLOOKUP to return the last month’s performance. This will always be the final column in the table (currently May-21).

The COLUMNS function will be perfect for this task, as every month the table will expand with another column when a new month is added. The COLUMNS function will always return the number of columns in the table, that is, the final column number.

So, this will always be correct even when the table expands. Much better than counting columns or having to update the column number manually each month.

In Figure 7-13, the following VLOOKUP formula is used to return the monthly total for each staff member. The COLUMNS function is used for the column index number argument to return the final column number from [tblMonthly].

=VLOOKUP(A2,tblMonthly,COLUMNS(tblMonthly),FALSE)

If the required column number was a specified number of columns from the end, this number could be subtracted from the COLUMNS result. For example, the following formula would return the previous month’s value:

COLUMNS(tblMonthly)-1

Note

There are also ROW and ROWS functions in Excel to perform the equivalent tasks but for rows instead of columns. We will see ROWS later in this book.

Trick 3: Using an Index Row

Another technique that you may come across is the creation of an index row. This is a row where the column index numbers of the table are entered and then referenced in the VLOOKUP.

Figure 7-14 shows an index row set up above a lookup table. Notice that the lookup table range begins in column B, but the column is indexed as column 1, ready for VLOOKUP. This lookup range is on a sheet named [Index Row Data].

This row is often hidden once the formulas are created, to avoid accidental damage and to remove clutter.

In Figure 7-15, the following formula is used to return the data from the [Status] column. It references cell F1 of the [Index Row Number] sheet to return the required column index number.

=VLOOKUP(E2,'Index Row Data'!$B$3:$G$11,'Index Row Data'!$F$1,0)

Referring to a cell value in this manner is another trick to avoid counting the columns of your larger lookup tables.

It is not as durable as using the COLUMN function can be, but it is very simple to set up and utilize.

Note

Another technique is to use the MATCH function to find and return the required column number. We will see this technique in Chapter 11, when we explore more lookup functions in Excel.

Reasons Why VLOOKUP Is Not Working

There are a few reasons why your VLOOKUP is not working. Let’s explore some of the common reasons along with their solutions.

Lookup Column Must Be the First Column

VLOOKUP looks for a value down the first column of a given range or array. If the column you need to look in is not the first (or leftmost) column of the table array, VLOOKUP will not find a match.

In Figure 7-16, the following VLOOKUP is returning the #N/A error as it cannot find a match for the ID value entered in cell B3. The lookup column is the second column of the range provided for the table array argument, $B$6:$E$9, and not the first column.

=VLOOKUP(B3,$B$6:$E$9,4,FALSE)

This can be fixed by reordering the columns of the lookup table so that the ID column is first or by using a different formula to VLOOKUP, such as XLOOKUP or the INDEX-MATCH function combination.

Another fix in this example is that you do not need to select all the columns of a table or range, only the ones that you need.

The following VLOOKUP formula uses the range $C$6:$E$9 for the lookup table (Figure 7-17). So, VLOOKUP now looks down column C and finds a match. Then it returns from column 3, as the salary is the third column of range $C$6:$E$9.

=VLOOKUP(B3,$C$6:$E$9,3,FALSE)

Data Type of the Values Must Match

This issue causes problems even for regular users of VLOOKUP. The data type of the lookup value and the value it finds must match, not just its value.

This issue commonly occurs when the table array is an export from an external source, and the numeric values are stored as text. However, the lookup value is a number.

In Figure 7-18, the lookup value in cell G3 is numeric, but the values in range $G$6:$G$9 are text values. You see their different alignment in the cells. VLOOKUP returns the #N/A error as it cannot match the lookup value.

The fix is to convert either the lookup value to text or the values in the lookup column to numbers. Which side you convert depends on what the value should be – text or number.

Figure 7-19 shows the values in G6:G9 converted to numbers and the VLOOKUP now working.

The Text to Columns command in Excel provides a quick way to convert text to numbers. You need only to select the values, click Data ➤ Text to Columns and then click Finish to close the window for a quick conversion.

We did discuss functions such as VALUE in Chapter 5 that will perform this task also.

This issue can be confusing and difficult to detect. Figure 7-20 shows the numeric values in range G6:G9 selected. They are left aligned and have a text format applied, so do not appear as numbers.

However, the VLOOKUP function is working, as the data type of cell G3 and the values in range G6:G9 do match.

When diagnosing issues such as this and it is unclear what data type a value is, the information functions such as ISNUMBER and ISTEXT are very helpful. They check if a value is a number or is text and will return TRUE if it is a match.

In Figure 7-21, the ISNUMBER function has been used to check the values in the original problem. The formula in cell I3 has confirmed that the value in cell G3 is a number, but the formulas in range K6:K9 show that the values in G6:G9 are not numbers.

Exact Match Has Not Been Specified

This is a common mistake for users that are new to using VLOOKUP. So, keep an eye out for this on spreadsheets set up by others that include VLOOKUP.

The final argument of VLOOKUP is range lookup. This argument is confusing when you are getting started with this function, it is optional, and TRUE is the default. These three factors seem to lead to the argument being missed or ignored.

If you require an exact match but do not specify one, you run the risk of having the wrong results and errors returned.

In Figure 7-22, the final argument is omitted when an exact match is required. You can see the incorrect result for ID 3992 in cell C6. There is also an error in cell C3 for ID 1841.

The values in the lookup table are not in ascending order by the first column, which is a requirement when performing range lookups. This is causing the chaos.

The fix for this problem is simple. An exact match needs to be specified for this VLOOKUP by entering FALSE or 0 in the final argument (Figure 7-23).

Ranges Must Be in Ascending Order

As we already know, the ranges need to be in ascending order when performing range lookups (approximate match lookups). If a lookup table has been ordered differently for any reason, this will present a problem for VLOOKUP.

In Figure 7-24, the VLOOKUP formulas in range J3:J8 are having trouble returning the correct results. This is because the scores in range L3:L6 are not in ascending order.

To fix this issue, the first column of the lookup table needs to be sorted into ascending order.

Figure 7-25 shows a functional VLOOKUP now that the scores are in the correct order.

=VLOOKUP(I3,$L$3:$M$6,2,TRUE)

Handling Fake Errors with VLOOKUP

Now, not all errors that VLOOKUP returns are real errors. VLOOKUP is very eager to return #N/A if it cannot complete its job. And sometimes there are logical and valid reasons why it cannot complete its job.

In these circumstances, an alternative action is desired over the error returned by VLOOKUP.

In this example, we have a simple claim form for mileage expenses in Excel. When a user specifies the site that they traveled to, we want a VLOOKUP to return the distance in miles of that site.

Figure 7-26 shows a lookup range with the locations that someone may travel to and the distance in miles.

Figure 7-27 shows the mileage claim form. There are some empty rows where there are no expenses to claim.

We would like to replace the #N/A errors with the value of 0. This is a more appropriate response, looks neater, and, most importantly, ensures the formulas will return the correct results.

There are two main functions for this task – IFERROR and IFNA. These functions use the same syntax and were covered in detail in Chapter 2, so we will dive straight in and use these now.

Although entering the value 0 is used in this example, we could have displayed any value or run an alternate formula.

The HLOOKUP Function

Availability: All versions

VLOOKUP has a lesser-known sibling – HLOOKUP. As you may have guessed, this stands for horizontal lookup.

Due to its less common usage compared to VLOOKUP, we will see only one example of HLOOKUP. However, every example demonstrated with VLOOKUP in this chapter can be performed with HLOOKUP, if required.

Figure 7-29 shows a lookup table/range with weekly performance values relating to some individuals. The headers are in the first column of the range (column B) with each field’s data stored in rows.

Get VLOOKUP to Look to the Left

One of the limitations of VLOOKUP is that it can only return a value from a column to the right of the one containing the lookup value (the first column).

Now this is not often a problem, as the first column of a table is typically a unique identifier. However, there is a trick to get VLOOKUP to look to its left.

Take the example shown in Figure 7-30. We need a VLOOKUP to return the grade for the score earned by each person.

Now, we could reorder the data in columns F and G, and VLOOKUP would work fine. But let’s look at a trick to get VLOOKUP looking to its left, and we do not need to reorder any columns.

This trick requires the use of the CHOOSE function in Excel. The CHOOSE function will change the order of the columns for VLOOKUP to work from.

The CHOOSE function returns a value, or performs an action, from a list of given values and actions. The value or action it chooses from the list is based on an index number.

The CHOOSE function is used in the table array argument of VLOOKUP. It is given the array of constants {1,2} for its index number. It is then told to return range $G$3:$G$6 first and $F$3:$F$6 second, therefore reversing the column order.

This is an array formula, so press Ctrl + Shift + Enter to run it if you are not using Excel for Microsoft 365, Excel for the Web, or Excel 2021.

Partial Match Lookup

A partial match is when only part of the lookup value text needs to match. Sometimes, you may be looking to perform a partial match instead of an exact text match.

For example, Figure 7-32 shows a table named [tblTargets]. It contains the performance targets for different offices that are in different cities. The [City] column contains the country name in addition to the city.

We need to match a city to one in the [City] column of [tblTargets]. However, we cannot get an exact match because the country name is also in those cell values. We need to perform a partial match.

The VLOOKUP function allows the use of wildcard characters directly in the function. This makes it easy to create partial matches with VLOOKUP.

The asterisk is joined after the city name in cell C3. Perfect for this scenario. We do not know how many characters will follow the city name, but we know there will be some.

Notice that the asterisk is enclosed in double quotations and joined using the ampersand.

Case-Sensitive VLOOKUP

Excel formulas rarely consider the case of characters when matching values. There are exceptions to this, for example, the FIND function is case-sensitive.

Figure 7-34 shows a VLOOKUP formula returning incorrect results for the lookup values written in lowercase – a and b.

Let’s look at how we can fix this by creating a case-sensitive VLOOKUP.

To create a case-sensitive VLOOKUP, the CHOOSE and EXACT functions will be used together. This is an advanced example that creates a two-dimensional array for VLOOKUP to use.

We saw an example of EXACT being used to match case in Chapter 2 of this book. Back then, we were working with the IF function. Let’s quickly remind ourselves on the EXACT function.

The EXACT function compares two text strings to see if they are exactly the same, including their case. It returns True if they are the same; otherwise, False is returned.

We saw the CHOOSE function two examples ago, when we made VLOOKUP look to its left.

This is an array formula. If you are using a version of Excel with the array engine, the formula will work as normal. Otherwise, you will need to press Ctrl + Shift + Enter to run the formula.

So, how does this work?

The VLOOKUP function has been given the lookup value of TRUE, so it now looks for TRUE down the first column of the two-dimensional array it has been given and returns the value from the second column.

Multiple Column VLOOKUP

It may be that you have two or more columns that need to be used in a lookup. VLOOKUP was not made with this scenario in mind, and it is much easier to perform with a function such as XLOOKUP or with a Merge Query in Power Query.

However, of course, it can be done. In Figure 7-36, we have a table (columns G:I) named [tblLastMonth] that contains a [Regional Code] column and a [Client ID] column along with last month’s [Total] value.

In column E, we need a VLOOKUP to return the [Total] value for the matching combination of regional code and client ID.

We will do this by first creating a new column in [tblLastMonth] with the combined [Regional Code] and [Client ID]. This column will be the one used for the lookup, so it needs to be the first column of the table.

To insert a new column in a table (not the worksheet)

1. 1.

   Right-click any cell in the [Regional Code] column of the table.

    
2. 2.

   Point to Insert and click Table Columns to the Left (Figure 7-37).

    
3. 3.

   Type the header “Reference” for the new column.

   Note You can also insert a table column by clicking Home ➤ Insert ➤ Insert Table Columns to the Left.

    

The following formula has been entered in the new [Reference] column to concatenate the [Regional Code] and [Client ID] column values (Figure 7-38):

=[@[Regional Code]]&[@[Client ID]]

The two lookup value columns have been combined to search in the first column of [tblLastMonth] and return the value from column 4.

This technique can easily be extended to work for more than two columns, if required.

Taking this a step further, you can create a multicolumn VLOOKUP using a single formula. So, we can achieve this without inserting a new table column and creating the merged [Regional Code] and [Client ID] column.

In Figure 7-40, the following formula is entered in cell E3. It uses the CHOOSE function to create an array (a virtual table you could say) consisting of two columns. The first column is a merged [Regional Code] and [Client ID] column and the second column is the [Total] column. The VLOOKUP then searches for the multicolumn lookup value in this array and returns the matching value from the second column.

=VLOOKUP(B3&C3,

CHOOSE({1,2},

tblLastMonth[Regional Code]&tblLastMonth[Client ID],

tblLastMonth[Total]),

2,0)

We saw the CHOOSE function recently when getting VLOOKUP to look to its left. This brilliant function is covered in more detail in Chapter 11.

Return the Nth Match

The VLOOKUP function always returns a value for the first occurrence of the lookup value that it matches, searching from top to bottom.

Now, if you want to return multiple results, this is not something that VLOOKUP can do. You want to use the FILTER function, covered in Chapter 13.

We can however use VLOOKUP to return a specific instance of the lookup value, such as the second, third, fourth, or nth instance.

In Figure 7-41, we have a lookup range that contains multiple instances of participant names and their scores in a contest. Each participant has three attempts, so they occur three times.

We will use a VLOOKUP to return all three scores to range C5:C7 for the name stated in cell C2.

Like the previous example, our first task is to create a lookup column in the first column position of the lookup table that we can use to uniquely identify each instance.

The COUNTIFS function (covered in Chapter 8) is used to count the occurrences of each name.

The interesting part of this formula is the criteria range given to COUNTIFS. It has an absolute cell reference at the start of the range and a relative cell at the end of the range. This ensures that the criteria range expands as the formula is filled down the cells of column E.

So, in range $G$3:G3, David occurs only once. But when the formula gets to the second occurrence of David, the criteria range is $G$3:G7 and includes the second occurrence.

The lookup value is a combined value of the name in cell C2 and the instance number from range B5:B7. When the name in cell C2 is changed, the scores for that participant are returned.

It is possible to achieve the same result using a VLOOKUP formula without the helper column identifying each unique instance. It is a more advanced formula.

So, how does this formula work?

The ROW function returns the row number of the worksheet, not the row number with range $F$3:$F$11. “Kelly” is matched to rows 4, 8, and 11.

The SMALL function then returns the Nth smallest number in this array. This number is specified by the values in range B5:B7. For the formula in cell C5, the SMALL function returns 4 as that is the first smallest number.

The CHOOSE function is used in the table array argument of VLOOKUP (Again! What a useful function!). It creates an array consisting of two columns.

In column 1, the ROW function returns a list of the sheet row numbers for range $F$3:$F$11. The result is {3;4;5;6;7;8;9;10;11}. Column 2 is simply the values from range $G$3:$G$11.

The VLOOKUP function searches for the row number returned by the SMALL and IF combo, in the column of row number provided by ROW, and returns the score value from column 2 of the array created by CHOOSE. Awesome!

VLOOKUP with Other Excel Features

As always, we will conclude the chapter with some examples of how VLOOKUP can be used with other Excel features. Let’s see two examples, one with Conditional Formatting and another with a chart.

Conditional Formatting and VLOOKUP

For a Conditional Formatting example, we want to format the values that exceeded a target score. The target score resides in a different table to the one with the formatted values, so we will use VLOOKUP to retrieve it for testing.

Figure 7-45 shows a list of staff, their locale, and their performance value. A second table lists the different targets by locale.

We want to format only the values in range D3:D9 that are greater than or equal to the respective target for that staff member’s locale.

1. 1.

   Select range D3:D9.

    
2. 2.

   Click Home ➤ Conditional Formatting ➤ New Rule ➤ Use a formula to determine which cells to format.

    
3. 3.

   Enter the following formula into the Format values where this formula is true: box (Figure 7-46):

    

=D3>=VLOOKUP(C3,$F$3:$G$9,2,FALSE)

Note

It is often easier to type a formula into a cell first and then copy and paste it into the Conditional Formatting rule. The formula box is quite small and offers little help, and you need to be careful to be in the correct cell mode when moving through the formula.

1. 4.

   Click Format and specify the formatting you want to use.

    
2. 5.

   Click OK to apply and close each window.

    

Figure 7-47 shows the formatting rule successfully applied to range D3:D9.

Dynamic Chart Range

In this chart example, we will create a dynamic chart range that changes on the selection of a value from a drop-down list.

In Figure 7-48, there is a table named [tblWeekly] with six weeks’ performance data by different staff members. Below the table are headers in preparation for our range that will feed the chart, and in cell J3 is a drop-down list of the different staff members’ names.

When someone selects a name from the drop-down list, we want the values for that selected staff member to show in the range below the table. The chart will be based on that range, so will change when the drop-down list value changes.

Note

Typically, the table of data, chart range, and chart with drop-down list would all be on different worksheets – a data sheet, calculation sheet, and a report/presentation sheet. It is displayed all-in-one here only to show the mechanics of the technique easier.

First, we want the selected name to appear in cell B12. This is achieved with a simple link to the value in cell J3. This cell value will be used as the chart title.

=J3

In range C12:H12, the following VLOOKUP formula is used to return the weekly values for the selected name (Figure 7-49):

=VLOOKUP($J$3,tblWeekly,COLUMN(C11)-1,FALSE)

The COLUMN function is used to return the column index number so that we do not need to enter it ourselves in six different VLOOKUP formulas, when we fill the formula.

It references the cell directly above the formula (C11 in the formula shown), and one is subtracted from the COLUMN function result because [tblWeekly] begins in column B. So, [Wk1] is the second column of the table, but the third column of the sheet.

With the chart range created, the chart can now be created. For this example, let’s create a simple line chart:

1. 1.

   Select range B11:H12.

    
2. 2.

   Click Insert ➤ Insert Line or Area Chart ➤ Line.

    
3. 3.

   Make the necessary improvements to the chart to make it look awesome.

    

Figure 7-50 shows a basic line chart with no modifications positioned underneath the drop-down list.

Note

Creating the chart is explained very briefly here as working with charts is not the purpose of this book. It is the formulas and the creation of the dynamic chart range that is our focus.

Summary

In this chapter, we learned the VLOOKUP function in Excel along with some tricks to creating a more robust and effective VLOOKUP formula and an understanding of common mistakes that users make. We then progressed to some advanced examples that included multi-criteria and case-sensitive lookups.

In the next chapter, we will look at formulas that perform aggregations such as sum, count, and average dependent on conditions that are met. There is a group of functions in Excel built for these purposes. This group includes SUMIFS, COUNTIFS, and AVERAGEIFS.

There are similar functions, also covered in the next chapter, just outside the scope of this group that includes MEDIAN, TRIMMEAN, and MODE.MULT.

Using functions such as these to aggregate values based on conditions is very popular in Excel. We have lots to cover, so let’s get started.