MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Eliminate VLOOKUP with Data Model

August 29, 2017 - by Bill Jelen

Eliminate VLOOKUP with Data Model

Avoid VLOOKUP using the Data Model. So, you have two tables that need joined with VLOOKUP before you can do a pivot table. If you have Excel 2013 or newer on a Windows PC, you can now do this simply and easily.

Say that you have a data set with product, customer, and sales information.

Data Set
Data Set

The IT department forgot to put sector in there. Here is a lookup table that maps customer to sector. Time for a VLOOKUP, right?

Time for a VLOOKUP?
Time for a VLOOKUP?

There is no need to do VLOOKUPs to join these data sets if you have Excel 2013 or Excel 2016. Both of these versions of Excel have incorporated the Power Pivot engine into the core Excel. (You could also do this using the Power Pivot add-in for Excel 2010, but there are a few extra steps.)

In both the original data set and the lookup table, use Home, Format as Table. On the Table Tools tab, rename the table from Table1 to something meaningful. I’ve used Data and Sectors.

Select one cell in the data table. Choose Insert, Pivot Table. Starting in Excel 2013, there is an extra box Add This Data to the Data Model that you should select before clicking OK.

Inser Pivot Table
Inser Pivot Table

The Pivot Table Fields list appears with the fields from the Data table. Choose Revenue. Because you are using the Data Model, a new line appears at the top of the list, offering Active or All. Click All.

Pivot Table Fields
Pivot Table Fields

Surprisingly, the PivotTable Fields list offers all the other tables in the workbook. This is groundbreaking. You haven’t done a VLOOKUP yet. Expand the Sectors table and choose Sector. Two things happen to warn you that there is a problem.

First, the pivot table appears with the same number in all the cells.

Pivot Table
Pivot Table

Perhaps the more subtle warning is a yellow box appears at the top of the PivotTable Fields list indicating that you need to create a relationship. Choose Create. (If you are in Excel 2010 or 2016, take your luck with Auto-Detect.)

Create Relationship in Pivot Table
Create Relationship in Pivot Table

In the Create Relationship dialog, you have four dropdown menus. Choose Data under Table, Customer under Column (Foreign), and Sectors under Related Table. Power Pivot will automatically fill in the matching column under the Related Column (Primary). Click OK.

Create Relationship Dialog
Create Relationship Dialog

The resulting pivot table is a mashup of the original data and the lookup table. No VLOOKUPs required.

Result Pivot Table
Result Pivot Table

Watch Video

  • Starting in Excel 2013, the Pivot Table dialog offers the Data Model
  • This is the code word for Power Pivot Engine
  • To use the data model, make a Ctrl + T table from each table in the workbook
  • Build a pivot table from the first table
  • In the Pivot Table Field List, change from Active to All
  • Choose a field from the lookup table
  • Either create the relationship or Auto-Detect
  • Auto-Detect was not there in 2013
  • Thanks to Colin Michael and Alejandro Quiceno for suggesting Power Pivot in general.

Auto-Generated Transcript

  • Learn Excel from MrExcel Podcast Episode
  • 2014 eliminate vlookup podcasting this
  • entire book click the I in the top right
  • hand corner for the playlist hey welcome
  • back to the MrExcel NetCast I'm Bill
  • Gellin this is actually called eliminate
  • vlookup with the data model now I
  • apologize this is Excel 2013 and newer
  • if you're back in Excel 2010 you have to
  • go download the power pivot add-in which
  • of course is free back in 2010 so what
  • we have here is we have our main data
  • set there's a customer field here and
  • then I have a little table that map's
  • customer to sector I need to create
  • total revenue by sector right this is a
  • vlookup just do if you look up but hey
  • no thanks to Excel 2013 we don't have to
  • do a vlookup I made both of these into a
  • table and on the table tools design I
  • rename the tables I call this one
  • sectors and I call this one data to make
  • it into a tailored suit choose one cell
  • press ctrl T ctrl T so if we have some
  • headings and some numbers when you press
  • ctrl T say they ask for is the data for
  • your table my table has headers and then
  • they call it table three you call it
  • something else all right that's how I
  • created those two tables I'm gonna get
  • rid of this table all right so for this
  • trick to work all the data has to live
  • in tables we go to the insert tab choose
  • pivot table and right down here at the
  • bottom add this data to the data model
  • this sounds very innocuous right there's
  • nothing like flashing pointing that is
  • saying hey it'll let you do amazing
  • things and what they're saying here what
  • they're trying not to say is is that oh
  • by the way every copy of Excel 2013 has
  • the power pivot engine behind it you
  • know if you're in office 365 you're
  • paying $10 a month and they want you to
  • pay 12 or 15 dollars a month to get
  • power pivot the extra two or five bucks
  • well hey shh don't tell you actually
  • have most of power pivot already in
  • Excel 2013 alright so I click OK takes a
  • little bit longer to load the data model
  • alright but that's ok and right over
  • here in the pivot table fields I have a
  • list of all the fields so I want to show
  • revenue certainly but what's different
  • is up here it active and all act
  • when I choose all I get all the tables
  • in the workbook alright so I go to the
  • sector's and I said I want to put sector
  • in the rows area now initially the
  • report is gonna be wrong see the 6.7
  • billion all the way down and this yellow
  • warning over here will say that you have
  • to create a relationship all right now
  • in 2010 with powerpivot it would just it
  • offered auto detect in 2013 they took
  • auto to detect out and in 2016 they
  • brought auto to detect back all right I
  • should show you a create looks like but
  • when I click this create button oh yeah
  • that's it that's it alright good so from
  • our first table data I haven't filled
  • called customer from the related table
  • sectors I have a field called customer
  • and then you click OK alright but let me
  • just show you how cool auto detectives
  • if you happen to be in 2016 there they
  • figured it out how awesome is that right
  • you don't need to worry about vlookup
  • and the comma falls at the end if you
  • look up makes your head hurt you're
  • gonna love the data model took those two
  • tables joined them together you know
  • like access would do I guess and created
  • a pivot table absolutely amazing so
  • check the data model next time you have
  • to do a vlookup between two tables well
  • this and all the other forty tips are in
  • the book click that I on the top right
  • hand corner you can buy the book have a
  • complete cross reference to this entire
  • series of videos all of August all of
  • September heck we might even carry over
  • into October for get the whole thing
  • done
  • alright recap today starting Excel 2013
  • the pivot table dialog offers something
  • called the data model it's the code word
  • for the power pivot engine before you
  • create your pivot tables do ctrl T to
  • make a table from each workbook I took
  • the extra time to name each one build a
  • tip of pivot table from the first table
  • and then in the field list go up to the
  • top and change from active to all choose
  • a field from the lookup table and then
  • it will warn you that you either have to
  • create a relationship or auto-detect in
  • 2013 you have to click create it but
  • it's what four clicks to to create it
  • five if you count the okay button
  • so really really easy to do alright
  • Colin Michael and Alejandro Insano
  • suggested PowerPivot in general for the
  • books thanks to them thanks to you for
  • stopping by
  • we'll see you next time for another net
  • cast from MrExcel

Download File

Download the sample file here: Podcast2014.xlsx

Title Photo: LoggaWiggler / pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.