Get 20-hours of Excel 2016 training here ► https://www.simonsezit.com/course-category/excel/

During this Microsoft Excel 2016 advanced training tutorial video, we will take a look at the VLOOKUP function and we will start to make the LOOKUP functionality more complex.

Check out some of our other training on YouTube:

Excel 2016 advanced training: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3h5ZVxzp_a4UHRQBtXXgnxf

Excel 2016 beginner training: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3hFUdlWMkTLZL-Fr3oRGN35

VBA for Excel tutorials: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3hWRi0mgxdyWkT0QaYKuBGZ

Project 2016 beginner training: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3g_ABHdUU7RoGJJm-YFr4_Y

Visio 2013 for beginners: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3jpAwkTGD6hGNxF8ML_FfsF

SharePoint 2013 training: https://www.youtube.com/playlist?list=PLzj7TwUeMQ3jloGuvewOe0Iv1EE2POKge

Stay in touch:
SimonSezIT.com: https://www.SimonSezIT.com/
StreamSkill.com: https://StreamSkill.com/
YouTube Channel: https://www.youtube.com/user/simonsezittraining
Facebook: https://www.facebook.com/SimonSezIT/

If you enjoyed the video, please give a "thumbs up" and subscribe to the channel ;-)

Hello again and welcome back to our course on Excel 2016 Advanced.

In this section we’re going to continue looking at Lookup functions and we’re going to in particular look at the VLOOKUP function. The principle in which this function works is pretty much the same as HLOOKUP except that instead of expecting a horizontal list of values to lookup it looks for a vertical list of values to lookup.
Now one of the reasons that I’m devoting a section to this function is that I’m also going to start to make the Lookup functionality here considerably more complex. And the way that we’re going to use the VLOOKUP function here is to do some more work on developing an invoicing system.

At the moment in Lookup_Functions_3 here I’ve got a few headings on a sheet and apart from the date at the top here which is today’s date and those headings there’s not much on it. But the general principle is that I’m going to put together a system whereby I can take an order from a client, list the details of the order and prepare an invoice.

Now basically in the body of the order, in this part here, I’m going to specify the number of an item that the client wants. So the quantity goes in there. A part number and then a description associated with that part number. There will then be a unit price, discount where applicable, a discounted price and a total price and then a grand total for the order down in cell H12 at the moment.

Now the first thing you may say is well where are all of these things coming from? Where do we get these prices from? Well on the second sheet in this workbook I have a catalogue. It’s a relatively small catalogue but it will demonstrate what I need to demonstrate perfectly well. And it’s a catalogue of parts. They’re actually plumbing, bathroom fitting parts. And each part has a part number in column A, a description in column B and a dollar price, undiscounted, in column C.

So basically what we need to do is when we put a part number in column C on the first sheet the first thing we’re going to do is look up the description. Now if somebody puts a part number in there that doesn’t correspond to an item in the catalogue we’ll want to put a relevant message in there. And we’re going to need to deal with various other complications as we go along, but let’s start with that basic functionality first.

Sorry, we couldn't fit the entire video transcription here since YouTube only allows 5000 characters.