Return All VLOOKUPs

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.