MrExcel's Learn Excel #1002 - Multiple VLOOKUP

If you like this content, please consider visiting the video on YouTube and subscribe to the MrExcel Channel to show your support!
This video has been published on Apr 28, 2009.
Peter asks how to return multiple records from a VLOOKUP. In Episode 1002, I will show you how to use an Advanced Filter to achieve this result.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
maxresdefault.jpg


Transcript of the video:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen Basically, we start out with massive amounts of data.
How we're gonna analyze this. Well, lets fire up a pivot table.
Let's see if you can solve this problem.
Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Well, today's question comes in from Peter.
Peter wants to know how to do a VLOOKUP to do multiple results.
So, he has you know a cell up here where we can choose a product and he wants to then see all of the orders for that product you can see the orders over here and there's of course, multiple orders.
Now, VLOOKUP is only going to return the first one.
So, I'm going to suggest that we don't use if you VLOOKUP at all that, we use something called, well, in the old Excel is called Advanced Filtering in the new Excel and it's out here on the Data tab it's in Sort&Filter and then just Advanced now.
So, this is a pretty powerful feature and that you can do a lot of different things with it.
You can use it to get a unique list of products in this case, we have our database here and I'm going to say that I want to Copy to another location the Copy to is going to have the headings order in quantity.
The Criteria range is going to be this place where we have the heading of product.
Which has to match this heading here and then the product that's chosen and we click OK.
Now, you see that it goes through and gives us all of those orders.
So, it's returning multiple values.
Now, in this original set over here if i would use control+T, to define this as a table and then i should be able to come, back into data here let's choose a different product.
So, C Advanced, Copy to another location, Click OK, and you see that we now have the orders for C.
Here's the real test we're gonna add a brand new item at the bottom D this is order 99999 and 99999.
And we'll come back into our Advanced filter. let's choose D's here Back into our Advanced filter and sure enough, it was smart enough to grow as the table groom.
So, we click Copy to another location and then OK and you see that we get that new order.
So, much better way to go of course, you're still gonna have to go up and run the Advanced filter.
Maybe, record a little macro to run that or something like that, but better than using VLOOKUP because it can return multiple results.
Wanna thank Peter for sending that question in.
And want to thank you for stopping by.
We'll see you next time for another netcast form MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
 

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top