MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Return All VLOOKUPs


May 16, 2018 - by Bill Jelen

Return All VLOOKUPs

Kaley from Nashville is working on a ticketing spreadsheet. For each event, she chooses a ticketing plan. That ticketing plan could indicate anywhere from 4 to 16 ticket types for the event. Kaley wants a formula that will go to the lookup table and return *all* matches, inserting new rows as appropriate.

While I don't have a VLOOKUP that can solve this, the new Power Query tools built in to Excel 2016 can solve it.

Note

If you have the Windows version of Excel 2010 or Excel 2013, you can download Power Query for free from Microsoft. Unfortunately, Power Query is not yet available for Excel for Android, Excel for ios or Excel for the Mac.

To illustrate the goal: Mike McCann and the Mechanics is appearing in the Allen Theatre with ticket plan C. Since there are four matching rows in the lookup table, Kaley wants four rows that say Mike McCann and the Mechanics, each with a different match from the lookup table.

Do a VLOOKUP, insert new rows for the matches
Do a VLOOKUP, insert new rows for the matches

Select a cell in the original table. Press Ctrl + T to mark that data as a table. On the Table Tools tab, rename the table from Table1 to Shows. Repeat for the lookup table, calling it Tickets.

Format both data sets as a table
Format both data sets as a table

Select a cell in the Shows table. From the Data tab, choose From Table/Range.

Run a query from the first table.
Run a query from the first table.

After the Power Query editor opens, open the Close & Load drop-down and choose Close and Load To....

Open the drop-down and choose Close & Load To...
Open the drop-down and choose Close & Load To...

In the Import Data dialog, choose Only Create a Connection.

Only create a connection
Only create a connection

Go to the Tickets table. Repeat the steps to Only Create a Connection to Tickets. You should see both connections in the Queries pane:

Connect to the lookup table as well
Connect to the lookup table as well

Select any blank cell. Choose Data, Get Data, Combine Queries, Merge.

A merge query is like doing a VLOOKUP
A merge query is like doing a VLOOKUP

There are six steps in the Merge dialog. The 3rd and 4th don't seem intuitive to me.

  1. Choose Shows from the top drop-down
  2. Choose Tickets from the second drop-down.
  3. Click on the heading for Ticket Plan in the top to select that column as the foreign key in the Shows table.
  4. Click on the heading for Ticket Plan in the bottom to select that column as the key field in the lookup table.
  5. Open the Join type and choose Inner (only matching rows).
  6. Click OK
Six steps in this dialog.
Six steps in this dialog.

The results are initially disappointing. You see all of the fields from table 1 and a column that says Table, Table, Table.

Click the Expand icon at the top of the Tickets column.

Expand the column from Tickets
Expand the column from Tickets

Unselect Ticket Plan since you already have that field. The remaining field will be called Tickets.Ticket Type unless you uncheck Use Original Name as Prefix.

Choose the field and prevent a geeky name
Choose the field and prevent a geeky name

Success! Each row for each show explodes into multiple rows.

Success
Success

I am not particularly happy with the sorting of the data. Sorting by Date causes the Ticket Types to sort in an odd way.

The sort order is unexplained.
The sort order is unexplained.

Watch Video

In today's case, the video was shot after the article was written. I suggest adding a sequence column to the Ticket Types to control the sort order.

Download Excel File

To download the excel file: return-all-vlookups.xlsx

Power Query continues to amaze me. This is the second of a three-day series where the answer is Power Query:

I have an entire YouTube Playlist of things that I ended up solving with Power Query.

Excel Thought Of the Day

I've asked my Excel Master friends for their advice about Excel. Today's thought to ponder:

"When in Doubt, use ROUND Function!"

Title Photo: Photo by Chaz McGregor on Unsplash