Excel is considered the best spreadsheet software in the world and with the release of the new Office 365 which includes cloud services and a monthly subscription fee, Excel should retain its title as the standard for spreadsheets. Excel offers over 400 built-in, easy to use functions that make data analysis and manipulation really simple. HLOOKUP is one of those functions and this tutorial will show you how to use the HLOOKUP function to lookup data within a table based on values you select.
This tutorial is designed for beginners, but some basic Excel skills are necessary to create the worksheets and follow the step by step instructions given in the tutorial. The Microsoft Excel 2013 Course Beginners/ Intermediate Training course from Udemy, is a great course for beginners and this course will teach you how to create workbooks, how to manage your workbooks and worksheets, and much more.
We have created a worksheet for this tutorial to show you how to use the HLOOKUP function. This worksheet contains the items, item codes and item prices for a fictitious grocery store.
This is what our fictitious grocery store worksheet looks like:
The HLOOKUP function is very similar to the MATCH and LOOKUP functions. The Excel Match Function: Ask and You Shall Receive, is an article that will show you how to use the MATCH function. If you would like to learn to use the LOOKUP function, then you can read The Excel LOOKUP Function: Power to Search your Spreadsheets.
How to Use the HLOOKUP Function in Excel
There are two ways to access the built-in functions that Excel offers. If you know the name of the function then you can type the function directly into the target cell where you want the answer to appear and also add the various parameters required for the function.
If you don’t know the function name or the parameters required, then you can select the function from the formulas menu in Excel. This tutorial will show you both methods of accessing the function.
To use a formula, you need to know what the formula does and what parameters the formula needs to calculate the answer or in this case, lookup the answer.
The HLOOKUP function searches a table or array within Excel for a specific value and then returns a corresponding value from the column of data.
Let’s examine how we can apply this function in our example data.
How to use HLOOKUP to Find the Price of an Item
Let us assume that we would like to type in an item name and have Excel look up the item price for us. To achieve this we need to use the HLOOKUP function.
To use the function, select the target cell you want the answer to appear in and then select the formulas menu from the menu bar in Excel. Select the Lookup & Reference tab to access the HLOOKUP function.
Select HLOOKUP and then add the parameters in the formula window. This is what the above HLOOKUP formula will look like:
The Lookup_value tells Excel what we are looking for. In this case we have typed in Apples so that Excel will search for “Apples” within our table. The Table_array parameter tells Excel which table to search for the value. The Row_index_num parameter tells Excel to return a value in a particular row if it finds the value we are searching for. Finally, the FALSE parameter tells Excel to find an exact match for our search term.
The HLOOKUP function searches for items in a table and returns the value in a corresponding row index. The VLOOKUP function searches a table or array and returns a value from a corresponding column index. If you data is stored in the form of columns instead of arrays, then the Vlookup Tutorial: 5 Easy Steps to Improved Data Sleuthing, will show you how to use the VLOOKUP function instead.
VLOOKUP and HLOOKUP are part of some of the more advanced functions available in Excel. For more details and step by step tutorials on how to use this function, and other advanced Excel functions, sign up for the Microsoft Excel – Advanced Excel 2010 Training course from Udemy and learn to harness the power of functions in your worksheets.
This is what the result will look like:
We can make it easier. Let’s assume there are a number of people working in the shop and they would like to look up the item and price without learning how to use the HLOOKUP function. Instead of using “Apples” as the Lookup-value parameter, we can assign a cell value to the lookup_value and then employees can merely type in the item name.
This is what our new formula will look like using C2 as the lookup _value:
If the employee types in Oranges now in C2, the Excel will search for “Oranges” and return the price for that item:
We can make this process even easier for our employees by creating a dropdown list for our employees to select, instead of having them type in the item name.
To create a dropdown list, select cell C2 – this is where we want out dropdown box to appear. Then select Data Validation from the Data menu:
To create a drop down list of all the item names, select list from the allow menu and then select the range of cells you want to include in the dropdown box.
You could also create a dropdown list by referring to named ranges within your worksheet. To learn how to work with named ranges and absolute ranges, sign up for the Learn Microsoft Excel 2013 – Advanced course from Udemy and learn the various functions in Excel 2013 and how to apply them to your data.
This is that the drop down list will look like in your worksheet:
Now employees can simply click on the drop down list, select an item and the corresponding price will automatically be reflected in the price column.
Whether you are looking for a job, or planning to use Excel at home to help you create a personal budget, Excel offers you the tools and functions that will make you life a lot easier. Sign up for the Fast Track to Microsoft Excel Beginner + Advanced Training today and learn to really apply the power that Excel offers its users.