The HLOOKUP function in Microsoft Excel searches for a specific value in the topmost row and column of a table in order to extract data from a table or array. If you are looking for some special tricks to use HLOOKUP with structured reference in Excel, you’ve come to the right place. There are numerous ways to use HLOOKUP with structured reference in Excel. This article will discuss five suitable examples of using HLOOKUP with structured reference in Excel. Let’s follow the complete guide to learn all of this.
What Is Structured Reference?
Generally, a structured reference is a term that refers to using a table name in an excel formula in lieu of a usual cell reference. We will consider it an absolute structured reference if the table name that we are using as a reference does not change when we copy the formula to another cell. The default syntax for absolute structured reference is:
Table[[Column_1]:[Column_2]]
For instance, we have selected the output cell H8 and typed the following formula to get the total value of January and February using the SUM function.
=SUM(Sales_3[Jan]:Sales_3[Feb])
Through this, we actually illustrate the structured references. The following image indicates the overview of structured references.
How to Use HLOOKUP with Structured Reference in Excel: 5 Suitable Examples
In Excel, we will use five practical and tricky examples to use HLOOKUP with structured references in Excel. This section provides extensive details on the five ways. You can use either one for your purpose, they have a wide range of flexibility when it comes to customization. You should learn and apply all of these, as they improve your thinking capability and Excel knowledge. We use the Microsoft Office 365 version here, but you can utilize any other version according to your preference.
1. Exact Match with HLOOKUP
Here, we are going to demonstrate how to use the HLOOKUP function with structured references in Excel. Our Excel dataset will be introduced to give you a better idea of what we’re trying to accomplish in this article. Using the chart or table below, you can see how many people have ordered certain foods on a given day over time. Column H lists the total number of items or orders that were canceled on each of those days. We can find out how many fried chickens were sold in that restaurant on August 7th, 2021. Let’s walk through the following steps to use HLOOKUP with structured references in Excel.
📌 Steps:
- First of all, select any cell randomly from the table range.
- In addition, go to the Table Design tab and type a name in the Table Name field. We named the table ‘Table_Data’. You can select any name based on your data. We will use this table name as a reference in the formula.
- Next, you have to select the output cell C15 and type the following formula.
=HLOOKUP(C14,Table_Data[#All],6,FALSE)
Here, C14 represents the lookup value, and ‘Table_Data [#All]’ illustrates the Table Array. In the 4th argument of the HLOOKUP function, we have defined the range_lookup as FALSE, which means the function will search for the exact match of the lookup item- ‘Chicken’ in the table.
- Then, press Enter.
- Consequently, the function will return with 132. So, a total of 132 pieces of fried chicken were sold on that particular date.
Read More: How to Use IF Function and Structured Reference in Excel
2. Approximate Match with HLOOKUP
Here, we’ll show you how to use the HLOOKUP function with structured references in Excel in a slightly different way. Sometimes, we forget what we’re looking for in a large amount of data. By setting the fourth argument to TRUE, the HLOOKUP function can be used with Approximate Match. If our search criteria partially match the desired exact name, the function will return the output cell if the exact data is located. Excel’s HLOOKUP function can be used with structured references, as demonstrated in the steps below.
📌 Steps:
- First of all, select any cell randomly from the table range.
- In addition, go to the Table Design tab and type a name in the Table Name field. We named the table ‘Table_Data2’. You can select any name based on your data. We will use this table name as a reference in the formula.
- Next, you have to select the output cell C15 and type the following formula.
=HLOOKUP(C14,Table_Data2[#All],6,TRUE)
Here, C14 represents the lookup value, and ‘Table_Data2 [#All]’ illustrates the Table Array. So, by using TRUE in the 4th argument, we’ve just got the proper match even after we’ve typed the name of a wrong item that is absent from the table. In cell C14, you can now enter additional food names or partially matched names from the table and observe the function’s output.
- Then, press Enter.
- Consequently, you’ll get the resultant value of 132.
Read More: Applications of Absolute Structured References in Excel Table Formulas
3. HLOOKUP with MATCH Function
Here, we’ll show you another example of how to use HLOOKUP with structured references in Excel. In this example, we apply the combination of HLOOKUP and MATCH functions. If you do not wish to repeatedly define the row number within the HLOOKUP function, but rather wish to change the date or value in cell C13 and obtain an immediate result, then you must use the MATCH function to define the row number of the data present in the corresponding cell. Cells C13 and C14 display, respectively, the date and the food item. Therefore, we wish to determine how many sliders were sold on that date in cell C13. Excel’s HLOOKUP function can be used with structured references, as demonstrated in the steps below.
📌 Steps:
- First of all, select any cell randomly from the table range.
- In addition, go to the Table Design tab and type a name in the Table Name field. We named the table ‘Table_Data3’. You can select any name based on your data. We will use this table name as a reference in the formula.
- Next, you have to select the output cell C15 and type the following formula.
=HLOOKUP(C14,Table_Data3[#All],MATCH(C13,Table_Data3[[#All],[Date]],0),FALSE)
In cell C15, the output will update whenever you change the date or name of the food item in cells C13 and C14.
- Then, press Enter.
- Consequently, you’ll get the resultant value as shown below.
🔎 How Does the Formula Work?
- MATCH(C13,Table_Data3[[#All],[Date]],0)
Here, C14 represents the lookup value, and ‘Table_Data3 [#All]’ illustrates the Table Array. Here, the MATCH function defines the row number of the data present in the corresponding cell. This function will produce the position of C13 in the array and return 7.
- HLOOKUP(C14,Table_Data3[#All],MATCH(C13,Table_Data3[[#All],[Date]],0),FALSE)
Here, cells C13 and C14 display, respectively, the date and the food item. In the 4th argument of the HLOOKUP function, we have defined the range_lookup as FALSE, which means the function will search for the exact match of the lookup item- ‘Sliders’ in the table and return 26.
4. HLOOKUP with Wildcard Character
By using a wildcard character- Asterisk(*) before and after a text, we can look for the exact word containing that text inside. It is a sort of alternative to the second method’s Approximate Match, but the use of the wildcard character asterisk will allow for a more precise exact search. On 7 August 2021, we want to find out how many orders were placed for a food item that contained the text ‘Lime’.
📌 Steps:
- First of all, select any cell randomly from the table range.
- In addition, go to the Table Design tab and type a name in the Table Name field. We named the table ‘Table_Data4’. You can select any name based on your data. We will use this table name as a reference in the formula.
- Next, you have to select the output cell C15 and type the following formula.
=HLOOKUP("*Lime*",Table_Data4[#All],6,FALSE)
Here, C14 represents the lookup value, and ‘Table_Data [#All]’ illustrates the Table Array. In the 4th argument of the HLOOKUP function, we have defined the range_lookup as FALSE, which means the function will search for the partial match of the lookup item- ‘Lime’ in the table.
- Then, press Enter.
- Consequently, you’ll get the resultant value as shown below.
5. Extracting Multiple Values from Array
Here, we’ll demonstrate a slightly different method for using Excel’s HLOOKUP function with structured references. We can extract multiple pieces of data based on the specified criteria by inserting an array containing the table’s row numbers inside the HLOOKUP function. For illustration, we will determine the quantity of limeade sold over a 5-day period starting on August 3, 2021.
📌 Steps:
- First of all, select any cell randomly from the table range.
- In addition, go to the Table Design tab and type a name in the Table Name field. We named the table ‘Table_Data5’. You can select any name based on your data. We will use this table name as a reference in the formula.
- Next, you have to select the output cell C15 and type the following formula.
=HLOOKUP(C13,Table_Data5[#All],{2,3,4,5,6},FALSE)
Here, C13 represents the lookup value, and ‘Table_Data5 [#All]’ illustrates the Table Array. In the 4th argument of the HLOOKUP function, we have defined the range_lookup as FALSE, which means the function will search for the partial match of the lookup item- ‘Limeade’ in the table.
- Then, press Enter.
- Consequently, you’ll get the resultant value as shown below.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article. It contains all the datasets and methods in different spreadsheets for a clear understanding.
Conclusion
That’s the end of today’s session. I strongly believe that from now, you may be able to use HLOOKUP with structured reference in Excel. If you have any queries or recommendations, please share them in the comments section below. Don’t hesitate. Keep learning new methods and keep growing!
Related Articles
- How to Lock a Structured Reference in Excel
- What is an Unqualified Structured Reference in Excel?
- How to Reference a Dynamic Component of a Structured Reference in Excel
<< Go Back to Structured Reference | Table Formula | Excel Table | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
