Excel VLOOKUP: Basics of VLOOKUP and HLOOKUP explained with examples

the main purpose of these two functions is to look up a value in a big data table and give you a corresponding value back that's sitting in the same row now we're going to keep in mind is that the value that you're looking up has to be on the left most column and the value that's being returned has to be on an adjacent column now left is the key word here so you can't look up a value on the right side and return a value on the left side so we look up an H lookup work in the same way except that V is for vertical lookups and H is for horizontal lookups so if your data table has column headers you're going to need we look up and if your data table has row headers you're going to need H lookup so let's have a look at an example for each for this demo imagine you've been given a large set of data and you've been told could these are the sales agents the quantity they sold the money they got the price they charged and their customers what you like to do is to create an overview agent report where you select the agent and you find the price and the customer from this table as a first step we're going to add our drop-down it's what we learned in an earlier lecture on data validation we're going to go to data data validation instead of any value select list and the source is our agent so press control shift and the down arrow key and now we get to select our agents from here now for the agent we select we'd like to find the price that's where our we look up formula comes in the first argument in the vlookup formula is the lookup value what we're looking up in this case we're looking up the agent which is in this cell next comes the table array you have to remember that this is the full table to the full range where your lookup values in and your answers are in and it's one range so you're not supposed to select different ranges by holding ctrl down and going like this but instead you select the entire range which in this case is from here to here kolima press again control shift down that's my range and keep in mind that the key the main thing is that this agent D is the first one in this range it has to be on the left hand side even if I don't need quantity and revenue I have to highlight them because they're in my way they're in the way of me getting to price and customer so you always have to select the entire range even if there's stuff in there that you don't need next is the column index number and that basically means how much should it move so it assumes that the first one is a 1 2 3 4 5 so basically in which column is the answer that you want we want the price and that makes it 1 2 3 4 the fourth column we have to put 4 in there and this last argument is important to get right if you're look up value is text another number if you leave it empty it defaults to true which means that an exact match is not necessary but it requires that your lookup column is sorted in ascending order if it's not sorted in ascending order and you leave this argument empty then you're probably going to receive an error in our case we have it sorted as ascending but generally I don't want to be bothered to think is this text as if the number is it sorted or not so I always put false in here meaning that these should be an exact match I recommend that you generally put this on false as well okay so let's check this agent D the price here is 39 rounded 39 now what about the customer I can follow the same logic again my lookup value is agent D the table I'm looking this up is here and my customers should be included which column is the customer the last one which may fit one two three four the fifth column and false is the last argument customer D for agent D customer age 37 for agent age I have a very simple dynamic report using vlookup you can also use vlookup within formulas so they don't have to be used on their own alone like this so for example if I wanted to find price here and let's assume I didn't have price in my source data I actually need to calculate it I could write a formula on this and I could say well divide the revenue by the quantity and to tell it which revenue to divide by which quantity I will use the vlookup formula and say look up agent age in this area I want the revenue first so that's number three false of the last argument and divide this by a result of the vlookup formula for quantity quantities number two and that gives me my price so agent H is 37 I think in C we look up doesn't have to be used on its own but can be used with in other formulas agent and is 42 customer in and now let me show you the H lookup version of this so in case your original source data is not provided in this way but instead in rows so I'm going to copy this we're going to move down here I'm going to paste this as transpose okay so in case you have your data provided to you in this way which is less likely but it's possible and you want to do the same type of look up and the same type of overview report you will have to use the H lookup function so in the same way if you have our sales agent let me just copy this down you write new formulas so in the same manner if you want to find the price of Agent n from this table we're going to write H look up the lookup value is this our table array is this don't forget that what you're looking up here needs to be the first in your table array next is the row index number instead of the column index so where is price in here 1 2 3 4 the fourth row and again do false the same with customer I can actually copy this paste this in here because this remains the same that's what I'm looking up my table is the same instead of 4 what number do I need 5 customer in agent piece here right at the end 40 is my price and customer P is their get if instead of price I wanted to find quantities the only thing I need to change is my index number and quantity is number 2 so I get 8 that's the H lookup function to recap one major limitation of the vlookup and the hlookup formulas is that they can't look to the left so what if we wanted it the other way round that instead of selecting the agent here we were going to select a customer and get back to the agent here we wouldn't be able to use the classical vlookup formula we're going to need to have to change the table by bringing customer to the left hand side because the value we're looking up always has to be on the left hand corner

Channel: Leila Gharani

Views: 1,118,387

Rating: 4.8858013 out of 5

Keywords: XelplusVis, vlookup, excel vlookup, excel vlookup tutorial, how to do vlookup, vlookup function, hlookup, vlookup with example, vlookup tutorial, excel vlookup example, how vlookup works, hlookup with example, vertical lookup, hlookup tutorial, Advanced Excel, Master Excel, Learn Excel Formulas, Excel formulas, Excel Tips, Excel Tips and Tricks, Best Excel Online Course, Excel Advanced formulas, Improve Excel skills, Excel for analysts, Microsoft Excel expert, Leila Gharani

Id: E7gQ-PgYkMc

Length: 10min 36sec (636 seconds)

Published: Sat Mar 04 2017