 Publishers of the world’s most comprehensive and up-to-date Excel tutorials ## Excel tutorials

The only Excel books you will ever need

• The only constantly updated Excel 365 titles more…
• Thoroughly covers Power Pivot and Power Query more…
• Covers business intelligence and OLAP features more…
• Simple, easy, understandable, 2-page lessons more…
• Available as both printed books and e-Books more…
• Used by schools, colleges and universities  more…
• Available for 365, 2019, 2016 and Mac versions more…

# 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.

Leave me a comment if you find the lesson useful.

## 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:

## 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.

I hope that you enjoyed the lesson and will now be completely confident creating any VLOOKUP exact match. This lesson is excerpted from the above book.

This is the only up-to-date Excel book currently published and includes an entire session devoted to the new Dynamic Arrays features.

It is also the only book that will teach you absolutely every Excel skill including Power Pivot, OLAP and DAX.

## Lessons in Session 3

 Session Three: Advanced Functions Lesson 3‑1: Understand precedence rules and use the Evaluate feature Lesson 3‑2: Use common functions with Formula AutoComplete Lesson 3‑3: Use the Insert Function dialog and the PMT function Lesson 3‑4: Use the PV and FV functions to value investments Lesson 3‑5: Use the IF logic function Lesson 3‑6: Use the SUMIF and COUNTIF functions to create conditional totals Lesson 3‑7: Understand date serial numbers Lesson 3‑8: Understand common date functions Lesson 3‑9: Use the DATEDIF function Lesson 3‑10: Use date offsets to manage projects using the scheduling equation Lesson 3‑11: Use the DATE function to offset days, months and years Lesson 3‑12: Enter time values and perform basic time calculations Lesson 3‑13: Perform time calculations that span midnight Lesson 3‑14: Understand common time functions and convert date serial numbers to decimal values Lesson 3‑15: Use the TIME function to offset hours, minutes and seconds Lesson 3‑16: Use the AND and OR functions to construct complex Boolean criteria Lesson 3‑17: Understand calculation options (manual and automatic) Lesson 3‑18: Concatenate strings using the concatenation operator (&) Lesson 3‑19: Use the TEXT function to format numerical values as strings Custom format strings recap Lesson 3‑20: Extract text from fixed width strings using the LEFT, RIGHT and MID functions Lesson 3‑21: Extract text from delimited strings using the FIND and LEN functions Lesson 3‑22: Use a VLOOKUP function for an exact lookup Lesson 3‑23: Use the SWITCH function Lesson 3‑24: Use an IFERROR function to suppress error messages Lesson 3‑25: Use a VLOOKUP function for an inexact lookup Lesson 3‑26: Use a MATCH function for an exact lookup Lesson 3‑27: Use the INDEX function Lesson 3‑28: Use the IFS function Session 3: Exercise Session 3: Exercise Answers

## Related Articles

In order to use all cross-lesson hyperlinks you will need to download the full e-book. The Expert Skills e-book contains 253 short lessons that will teach you everything there is to know about Excel.

## Which tutorial do you need?   ### Which Excel Essential Skills version do you need to learn?

#### Excel 2016 for Mac 