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.
Learn Excel from MrExcel Podcast, Episode 2204: Return All VLOOKUPs.
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen. Today's question from Nashville Music City. I was down there in Nashville, someone is responsible for scheduling loading tickets into a ticketing system and so here's what we have: We have a list of events-- upcoming events-- we have the date, the venue, and a ticket plan. So, like, even though something's held at the Palace, there might be different ticket plans-- like, maybe the floor is configured, you know, with seats or maybe it's just a standing room only, right?
So, depending on what type of ticket plan, you have to come over here to the Lookup table and find all of the matching events, and essentially we're going to do what I call a VLOOKUP explosion. So if something is at the Hannah C, they're going to go down to Hannah C and if there are-- 1, 2, 3, 4, 5, 6-- 7 items in Hannah C, we're going to have to return seven rows-- which means you're going to have to insert six more rows and copy that data down. Alright.
Now, we're not going to do this with a VLOOKUP at all, but you get the concept-- we're doing a VLOOKUP and we're returning all of the answers as new rows. Alright, so, I'm going to take both of these tables and make them into a real table with Ctrl+T. The first ones called Table 1-- horrible name, let's call this Events or Shows, let's call it Shows, like that-- and the second one, now, hey, here's what I learned because I practiced this-- we have to have a sequence field here. So =ROW(A1), double-click, and copy that down and then copy and paste special values. Alright. Now we make that will make that into a table-- Ctrl+T, and we'll call that one Tickets.
Alright. So we have shows, we have tickets. I'm going to go to the Data tab, and I'm here in the shows thing, I want to say that I want to get my data from a Table or Range-- this is Power Query, by the way. If you're back in Excel 2010 or 2013, you can download this for free from Microsoft, download the Power Query tool. If you're on a Mac or iOS or Android, sorry, no Power Query for you. Alright, so from a Table or Range... find someone who has a-- find a friend who has a-- Windows PC and have them set this up. Alright. Here's a table, we're not going to do a thing to this, just Close & Load, Close & Load to, and then say "Only Create Connection", perfect. We're going to come over here to our second table: Get Data, From a Table or Range, we're not doing anything to this one, Close & Load, Close & Load to, "Only Create Connection", OK. So what we have now, is we have a connection to the first table and a connection to the second table. We're not going to merge these two, which essentially is like doing VLOOKUP, or a Database Joint, I guess, is really what it's like. Combine Queries, we're going to Merge. Alright.
Now, seven things you have to do in this dialog box-- and it's a little confusing-- we're going to choose Shows as the first table; choose Tickets as the second table; choose what field they have in common, and this can be multiple fields-- you can control-click-- but in this case there's only one ticket plan; and then Ticket Plan; and then we're going to change the Join type to an Inner join with "only the matching rows". Alright. Now, you click OK and you think that your whole problem's going to be solved, but you're just crushed because here's all of the data from A-- they haven't inserted any new rows at all-- and over here, just a boring stupid field called Tickets that just has Table, Table, Table, hah.
But, thankfully, at the top of that is an Expand icon, and we're going to expand that-- I don't need to take a plan, I already have that-- Ticket Type and Sequence. I don't want to have it be called Tickets.TicketType, which is what Power Query wants to do-- so I uncheck this box. Alright. Right now we have 17 rows of data; when I click OK, BAM! There's the explosion. So, Michael Seeley and the Starlighter's shows up with all of the different ticket types, like this. Alright, and see these ticket types appear in sequence, that's great. But Michael Seeley isn't the next show, the next show is on June 5th. So when I try and sort this by Date-- this drives me insane, I can't explain this. Sort by Date, and Mike Man and the Mechanics comes up to 65, but then the tickets are all screwed up. They're on the wrong sequence, and then that's why I had to do this sequence-- feels that way. I can sort by Sequence. So now, 6, 5, beautiful, and then within that, the Tickets are correct. And actually, at this point, we don't need this column anymore. So I can right-click and remove, and then Close & Load-- this time I'm going to actually Close & Load, not Close & Load to-- and we have our result. Alright.
So we went from a list of events to this whole big list, but here's the awesome part: I screwed this up, Mike Man and Mechanics isn't Palace B, its Palace C. So I come back to the original data-- let me make sure I spell it right, Palace C, Palace C-- I change the ticket plan from Palace B to Palace C, and then I come back to my results where we had 107 rows loaded. I'm going to have it do all of that again-- just click the Refresh icon over here. And hey, by the way, there's a bug you don't see the Refresh icon initially, you only see that-- you have to drag this out, drag it out, so you can see the Refresh icon. So, right there, Mike Man and the Mechanics had six floor levels plus a balcony, when I click refresh it, updates and Palace C just have Pit 1 and Pit 2, we don't sell out anything in the balcony. And now 102 rows loaded. set this up once, get more data, just type the data, click Refresh, it's a beautiful, beautiful trick.
Now, Power Query is covered in my book, MrExcel LIVe, The 54 Greatest Excel Tips of All Time. Power Query is an amazing, amazing tool. Click that "I" on the top right-hand corner for more information about the book.
Alright. Topics in this Episode: Kaley in Nashville needs to do a VLOOKUP to return all of the matches, usually inserting new rows. And it's a ticketing database, alright? So I'm going to call this a VLOOKUP Explosion because each show will explode into up to 16 rows. We're going to use Power Query to solve this, and I've learned that the Date is going to appear on the wrong Sequence unless we add a Sequence field to the ticket type. Make both sets into a Table with Ctrl+T; eename them to be Shows and Tickets; and then from each table, Get Data, From Table, Close & Load, to Only create a connection; repeat for the other table; then Data, Get Data, Combine Queries, Merge; and then that dialog box, it's pretty confusing to me-- choose Events, choose Tickets, click on Ticket Type in both, change the joint to an inner join, click OK, and then you get that horribly disappointing result where it's just a column that says Table, Table, Table, Table; click the Expand icon at the top of that; choose Ticket Sequence field; don't prefix with the name of the table; and you can Sort by Date, Sort by Sequence; Close & Load to the spreadsheet. The beautiful thing is that if the underlying data changes-- just Refresh and you have your results.
Now, hey, to download the workbook used from today's video, visit the URL down there in the YouTube description. Also a list there of the upcoming seminars-- I'd love to see you at one of my live Power Excel seminars.
I want to thank Kaley for showing up in Nashville and giving me that great question. I want to you for stopping by. I'll see you next time for another netcast from MrExcel.
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