VLOOKUP exact match

This lesson shows how to perform a VLOOKUP exact match (almost all VLOOKUPS found in business workbooks use exact matches).

In my classroom courses students always have VLOOKUP at the top of their list of skills they need to take back to the office. I find it surprising that so many students have difficulties using VLOOKUP. Absolutely anybody can master VLOOKUP in a few minutes by working through the short lesson below.

This article is an unedited lesson from one of the 28 short focused lessons in the Advanced Functions Session of my “Expert Skills” book.

Leave me a comment if you find the lesson useful.

Last Updated: September 28, 2020

note

The lesson teaches you to create a VLOOKUP exact match. It is also possible to use VLOOKUP for an inexact match

Unlike a VLOOKUP exact match, an inexact match will not always return an error if a value is not found. Instead it will return the nearest value found.

Almost all of the time you will want VLOOKUP to use an exact lookup.

The VLOOKUP Inexact match type is covered in depth in: Lesson 3‑25: Use a VLOOKUP function for an inexact lookup.

important

The XLOOKUP dynamic array function has now replaced VLOOKUP in Excel 365

The Excel 365 July 2020 semi-annual version (2002) added support for dynamic arrays to Excel.

Dynamic arrays are a huge step forward and change best-practice for many business tasks in Excel 365.

Unfortunately dynamic arrays are incompatible with Excel 2019 and earlier versions.

If your workbooks need to be used by non-Excel 365 users you will not be able to use functions that rely upon dynamic arrays.

XLOOKUP is the new dynamic array function that completely replaces VLOOKUP. It is easier to use and far more powerful. If you do not need to share your workbooks with users of older Excel versions you should no longer use VLOOKUP in new work.

Because VLOOKUP was one of the most used Excel functions in the past, you will need a good understanding of it in order to understand legacy workbooks that were created using older version of Excel.

You’ll learn about dynamic arrays later, in: Session Four: Working with Dynamic Arrays.

You’ll can read two lessons about the modern XLOOKUP replacement for VLOOKUP in:

Excel XLOOKUP Function (part one)
and
Excel XLOOKUP Function (part two)

important

It is best practice to use tables with your VLOOKUP functions

In this lesson I’ve shown you how to construct a VLOOKUP function that uses a table for the Table_array argument.

This has been best practice since the release of Excel 2007, but it wasn’t possible in Excel 2003 as the (fantastically useful) table feature wasn’t available.

In worksheets constructed using pre-2007 versions of Excel (or those created by novice users) it is common to see absolute range references for Table_array arguments like this:

More sophisticated users of pre-2007 Excel versions learned how to use Range Names. (You’ll learn all about Range Names in: Session Five: Using Names and the Formula Auditing Tools).

When you see a Range Name reference, it looks the same as a table reference. This example shows the use of a Range Name also called Stock:

While Range Names were best practice in pre-2007 Excel versions, they have a fatal flaw as they are not truly dynamic.

Users of earlier versions had to resort to a complex work-around to make their Range Names dynamic. You’ll learn about this workaround in: Lesson 5‑6: Create dynamic formula-based range names using the OFFSET function.

This information is provided so that you will understand any older Excel worksheets you may inherit, (or worksheets that were created by users that haven’t yet learned how to use tables).

Lesson 3 22: Use a VLOOKUP function for an exact match

Consider the following worksheet:

The retailer has created a stock code to save time when creating invoices. The code is made up of the first two letters of the watch description, along with the last two numbers of the date of manufacture.

When provided with a stock code, the VLOOKUP function can scan all of the codes in column A until a match is found and then return a value from the same row for any of the other columns.

1. Open the sample file Invoice-1 in Excel.

In this lesson, you will create a VLOOKUP formula that will automatically return the Description of any watch into column B when the user enters a stock code into column A.

2. Convert the range A5:G15 on the Stock worksheet into a table named: Stock

This was covered in: Lesson 1‑13: Convert a range into a table and add a total row and Lesson 1‑19: Name a table and create an automatic structured table reference.

When working with the VLOOKUP function it is best practice to use a table for the Table_array argument (see sidebar on facing page).

Using a table will make the data dynamic. In other words, the VLOOKUP function will still work correctly if you add and remove rows from the Stock table.

3. Insert a VLOOKUP function into cell B6 on the Invoice worksheet to find the description that matches the Code in cell A6 on the Stock worksheet.

Click in cell B6 on the Invoice worksheet.
Click: Formulas->Function Library->Lookup & Reference->
VLOOKUP

The VLOOKUP Function Arguments dialog appears. It can be seen that the VLOOKUP function has three required arguments (shown in bold face) and one optional argument:

4. Set the Lookup_value argument to: A6

This is the cell on the Invoice worksheet that provides the value to be searched for in column A of the Stock worksheet. You want to look up the description for the watch that has the code CA74. This is contained in cell A6.

5. Set the Table_array argument to: Stock

The table array is the range, table or name (see sidebar) you will search for a match to the value in cell A6. VLOOKUP always searches the left-most column of the range, table or name.

Type: Stock into the Table_array text box.

It is best practice to use a table for the Table_array argument (see sidebar).

6. Set the Col_index_num argument to: 2

Counting from left to right, the Col_index_num argument is the column that contains the value you want to return. In this case, it is the Description column, so you want to return column 2.

7. Set the Range_lookup argument to: FALSE

Beginners often overlook this vital argument because it is optional.

If it is left blank, VLOOKUP will return an inexact match. Later, in Lesson 3‑25: Use a VLOOKUP function for an inexact lookup, you’ll find out why that might be useful, but in this case you want an error to be returned if the stock code is not found, so it is vital to set this argument to FALSE. This type of lookup is called a VLOOKUP exact match.

8. Click the OK button.

The description of the Cartier Tank 18K is returned to cell B6.

9. Save your work as Invoice-2.

Here’s the sample file at the end of the session. If you ran into any problems with your VLOOKUP exact match you can study the finished workbook to identify the problem.