Return All VLOOKUPs
May 16, 2018 - by Bill Jelen
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.
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.
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.
Select a cell in the Shows table. From the Data tab, choose From Table/Range.
After the Power Query editor opens, open the Close & Load drop-down and choose Close and Load To....
In the Import Data dialog, choose 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:
Select any blank cell. Choose Data, Get Data, Combine Queries, Merge.
There are six steps in the Merge dialog. The 3rd and 4th don't seem intuitive to me.
- Choose Shows from the top drop-down
- Choose Tickets from the second drop-down.
- Click on the heading for Ticket Plan in the top to select that column as the foreign key in the Shows table.
- Click on the heading for Ticket Plan in the bottom to select that column as the key field in the lookup table.
- Open the Join type and choose Inner (only matching rows).
- Click OK
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.
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.
Success! Each row for each show explodes into multiple rows.
I am not particularly happy with the sorting of the data. Sorting by Date causes the Ticket Types to sort in an odd way.
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:
- Tuesday: Convert a column of Date/Time to just date
- Today: Return All VLOOKUPs
- Thursday: Create a Survey for Each of 1100 items
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