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.

**Table of Contents**hide

## 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.

## 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.

## 5 Suitable Examples to Use HLOOKUP with Structured Reference in Excel

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.

## 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 forget to check our website, **ExcelDemy.com** for various Excel-related problems and solutions. Keep learning new methods and keep growing!