RENAE CLARK: Hello, and welcome to this video lecture on using the HLOOKUP function in Microsoft Excel. What does the lookup function do for us? Well, a lookup function will help you find values in a table of data and insert them in another worksheet location. The syntax for a lookup function-- this is the HLOOKUP, and it's very similar to the VLOOKUP. The only change is our third argument is row index number instead of column index number. 

So we have =HLOOKUP, open parentheses, followed by three required arguments-- the lookup value, comma, table array, comma, row index number, comma, followed by the optional range lookup and a closing parentheses. The range lookup is optional because if you leave it blank, it's going to assume that it is an approximate match. So let's go back to the beginning. 

Lookup value represents the information that you're going to want to look up in your table array. The table array has a range of cells that contain a set of information-- in this case, in rows-- that you're going to look for what's in the first row and bring something back from either the second row or a later, higher row number, depending on how large your table array is. The row index number represent which row you would like to return. 

So if you have only two rows in your table, you will always be returning the row index number two. If you have three, you could return two or three, and so on. Row one will never be returned because that's the value you're looking up. 

The range lookup, approximate match versus an exact match. When do you use which? Well, an approximate match is when the lookup value is found within a range of numbers or items that's in the first row of your lookup table. So if it can be this up until the point that you hit, say, maybe it's number of years. So if it's from anywhere from zero to up to four years, then you would have an approximate match. If it's over four years or greater, then it would be a different number. This is the situation where you might use an approximate match. 

Then exact match is when the lookup value must match one of the values found in the first row of the lookup table exactly. So if I have product IDs, perhaps, those are what I'm going to be looking up, and I'm going to bring back product description, it must be an exact match because a product ID represents usually only one product. 

Here in this worksheet, I have set up a row-based table array. In the first row, I have years of service. In the second row, I have the award amount. I have named this Service Award, and it's going to be used in the calculation of the service award payout. The service award payout is calculated based on the number of years of service for each employee. 

So n into-- I'm going to type in my HLOOKUP. I could go ahead and use the function arguments dialog box, or I could type it in if I already know the syntax. The lookup value that I'm going to be using, again, is years of service. That's located in column G, so I'm going to put in G2. The table array was service award. 

Now, the row that I'm going to bring back because I'm looking up the years of service in row one, the row index number that I want to pull back is the dollar amount of the award. It's found in row two, so I enter a row. This is an approximate match, so I may leave this blank, or I could put True in here. Either way should get me the correct result. This is an approximate match. I'm just going to go ahead and type True in to ensure that it's working. 

So here you can see I have an employee who has 20.9 years of service received $4,000. One who has 2.4 years of service receiving $500. Let's take a look at our table to verify that it's working. So two years, two, but not five, $500. 20.9 years but not 25, so $4,000. So that's how our HLOOKUP works. 

The syntax is fairly straightforward. Starting with an equal sign, =HLOOKUP, open parentheses. The first argument, G2, represents the lookup value. In this case, it's this information in the years of service column. The service award amount the second argument representing the table array. Two is the third argument representing the row index number. And I've entered True for the range lookup because this is an approximate match, and I follow with a closing parentheses. 

More of my lectures can be found on YouTube by searching for Renae K. Clark and then subscribing to my channel. You will find a variety of Excel lectures available, as well as selected other lectures.