An in depth tutorial on the VLookup and HLookup functions

This video gives a detailed step by step tutorial on how the VLookup and HLookup functions work and explains each part in detail, enabling you to become an Excel Master

Want to try it yourself?? See the link below for a copy of the Excel worksheet that I used in the video
https://1drv.ms/x/s!Al4fWJrdGuuibhc1VyutZ-PbtPA

Index-Match Tutorial:
https://youtu.be/VSMJO4iXRvw


Overview:
1. In this video we start off with a brief explanation of what the VLookup and HLookup functions work, and how they differ from each other. Namely, the Vlookup function searches a vertical list for a specific value and will return the value from an adjacent cell in another column. The HLookup on the other hand searches a horizontal list for a specific value and will return the value from an adjacent cell in another row. Keep in mind that they are one directional and VLookup only works top to bottom and HLookup right to left.

2. We then jump into the equations used in the VLookup and HLookup functions by opening the VLookup formula window and going through each of the arguments one by one.

3. We then jump into some practical examples and use the VLookup to find the age and savings of individuals by using the VLookup function.

4. The Range Lookup argument in the VLookup and HLookup formulas create a lot of confusion for users and so we discuss in detail how this works. It is key to note that if the range lookup argument is left blank then Excel will assume that your list is in order and will return the closest match.

5. And finally we look at some comman errors that pop up when using the VLookup and HLookup formulas. The #N/A error (value not available error) tells you that the item you are searching for is not on the list or possibly that you have left the Range Lookup blank when you shouldn't have.
The #REF error (invalid cell reference) appears when you enter an index number (the row or column you want the value from) that is larger than the size of your range.
And finally the hardest error to spot. If you leave the Range Lookup blank when you actually need an exact match you may not get an error indicator but you may get the wrong information. This is very difficult to pick up and so you need to assure that you enter 0 for the Range Lookup when you need to.





VLookup HLookup Index Match Functions Excel 2013 2016