Excel 2024: Eliminate VLOOKUP or XLOOKUP with the Data Model

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

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

There is no need to do VLOOKUPs to join these data sets if you have Excel 2013 or newer. These versions of Excel have incorporated the Power Pivot engine into the core Excel.

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.

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. Surprisingly, the PivotTable Fields list offers all the other tables in the workbook. This is ground-breaking. You haven't done a VLOOKUP and you won't have to. 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.

Perhaps the more noticeable warning is a yellow box that appears at the top of the PivotTable Fields list, indicating that you need to create a relationship. Choose Create. Try your luck with Auto-Detect - it often succeeds.

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 Related Column (Primary). Click OK.

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

Bonus Tip: Count Distinct

To see an annoyance with pivot tables, drag the Customer column from the Data table to the VALUES area. The field says Count of Customer, but it is really a count of the invoices belong to each sector. What if you really want to see how many unique customers belong to each sector?

Double-click the Count of Customer heading. At first, the Summarize Values By offers choices such as Sum, Average, and Count. Scroll down to the bottom. Because the pivot table is based on the Data Model, you now have Distinct Count.

After you select Distinct Count, the pivot table shows a distinct count of customers for each sector. This was very hard to do in regular pivot tables.

Thanks to Colin Michael and Alejandro Quiceno for suggesting Power Pivot.