Essential to Expert Skills progression

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.  

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.

 

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:

VLOOKUP Exact Match sample file at the start of the lesson

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.

VLOOKUP Exact Match sample file at the start of the lesson

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:

VLOOKUP exact match function arguments dialog

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.

VLOOKUP exact match lookup value argument.

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.

VLOOKUP Exact Match function sample file at the end of the lesson.

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.

Learn Excel 365 Expert Skills with The Smart Method (fourth edition) - Front cover

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

VLOOKUP inexact match illustration (chicken and egg).

VLOOKUP inexact match

VLOOKUP lesson with sample file that will teach you everything there is to know when creating a VLOOKUP inexact match Excel function.

x-lookup-tutorial-part-one

Excel XLOOKUP function (part one)

The new Excel XLOOKUP function was introduced in the July 2020 Excel 365 semi-annual update.  It isn’t available in older versions (Excel 2019 and earlier). 

Adding images to tables

Adding images to tables

This article explains how to insert pictures into Excel workbooks and Excel’s current image features, including cell backgrounds.

Table tools design tools group

Table Tools, Design Tools Group

This article explains the Table Tools > Design tab on the Excel Ribbon, how to access it and how to reset the Ribbon if the tab has been disabled.

Share this article

Share on facebook
Share on twitter
Share on linkedin
Share on stumbleupon
Share on email

Leave a Reply

Your email address will not be published.

seventeen + eighteen =

Cross-lesson hyperlinks

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.

When you download the e-book you’ll be able to access every lesson,  online or offline, on all of your devices, using Amazon’s free advanced e-book reader