If you have a list of data from which you frequently need to look up lots of items at once, then this VLOOKUP technique will be a lifesaver. Plus it's really easy to set up! Download the file used in this video here: http://software-matters.co.uk/microsoft-excel-help-and-tips.html#download
Get more help and advice on Excel, Access or VBA projects:
Two Step - Huma-Huma
YT Creator Resources
If you found this video helpful, please consider liking, sharing or commenting.
Visit the Software-Matters website:
Software-Matters is based in Gillingham, Dorset, in the south-west of the United Kingdom (UK), near Somerset, Wiltshire and Hampshire and the cities of Bournemouth, Poole, Southampton, Bristol, Bath and Salisbury.
Imagine you have a giant list of products in Excel, and you've just received a request for a quote for a large number of them. You need to get some information from the spreadsheet about each product, which means finding each one manually or using the built-in find menu and copying out the information from individual lines.
The more regularly you do this the more tedious it will become, but luckily there is a way to make the task quick and easy. We're going to show you how you can set this up in just a couple of minutes.
So here is our giant list of product data. First we need to make a place to store the list of things that identify the products we want to see and display the information after it is looked up. For example, here I think I'll add a new sheet and make a column to store part numbers to look up, as each row in my main data has a unique number. All I need to do is add a heading which I'll call Part Numbers Requested at the top of a column. I'll add a couple of part numbers from my main list as examples.
Now I'm going to make one formula to look up the part number on each line in our target list, and bring back the description, and another one to bring back the price.
The central part is a VLOOKUP function, looking up the part number in the data range on my main sheet, selecting column 2 in this range as that is where the description is, then setting the final argument to FALSE so it looks for exact matches. This gets me my description. In the next column I'll do the same thing but looking at column 4 which is the sale price. Note that the part number needs to be the first column of the range we're looking things up in for VLOOKUP to work.
So that's one row sorted. I'll copy this down to give me a large landing area for my target part numbers.
If the VLOOKUP can't find the part number in the target list, or there is no target number given as in most of the cases here, it returns this #N/A error. This isn't anything to worry about, and will go away once a valid part number is asked for.
By this point I've actually got all I need to do my multiple item lookup. So let's say I've been given this list of twenty part numbers to look up. I simply paste them into my multiple lookup list and the details from the main list appear. I can paste news lists in at any time to get new information, and copy the information back out to where I need it, all instantaneously.
So there is a simple way to look up multiple items at once in a big list. The following steps are to make it look more professional if that matters for what you are using it for.
For example, if you don't like the look of all those #N/A errors, you put an IFERROR function around those VLOOKUPs. The first argument of the function is what you are evaluating; the second is what will happen if it turns out to be an error. A simple option would be to have nothing happen, which is represented by two quotation marks, or perhaps you'll have a message saying the part number was invalid. Note that IFERROR only works in Excel 2007 and after; if you have an earlier version, you can use IF and ISERROR functions in combination to do the same thing though.
Whether you need cosmetic changes or not, the final result is quick to set up and even quicker to use, making back all the time you spent implementing it after just a couple of searches.
One slight annoyance you may encounter is expanding your lookup list as you need to copy down the new formula into each column every time, or expanding your main list as you need to change the range that your VLOOKUPS are looking in. This can be avoided using Excel Tables, which we shall be covering in more detail in an upcoming video, so subscribe to make sure you don't miss it
It will also be linked in the description and on screen now when it is out, in which case you can click through to that video right away.
Aside from that, to see more Excel, Access and VBA tips to try, click the links on screen or above in this description. Thanks for watching!