In Microsoft Excel, HLOOKUP or Horizontal Lookup function is generally used to extract data from a table or an array based on searching for a specified value in the topmost row and the corresponding column. In this article, you’ll get to learn how you can use this HLOOKUP function efficiently in Excel.

The above screenshot is an overview of the article, which represents an application of the HLOOKUP function in Excel. You’ll learn more about the dataset as well as the methods to use the HLOOKUP function properly in the following sections of this article.

**Introduction to the HLOOKUP Function**

**Function Objective:**

**HLOOKUP function searches for a value in the top row of a table or array of values and returns the value in the same column from the specified row. **

**Syntax:**

**=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])**

**Arguments Explanation:**

Argument | Compulsory/Optional | Explanation |
---|---|---|

lookup_value | Compulsory | The value to be looked for in the table. |

table_array | Compulsory | The table or an array of cells where the specified value will be looked for. |

row_index_num | Compulsory | Position of the row in the table from where the data will be extracted based on the column containing the lookup value. |

[range_lookup] | Optional | Lookup criteria. TRUE for Approximate Match and FALSE for Exact Match. |

## How to Use HLOOKUP Function in Excel: 8 Suitable Approaches

**1. Exact Match with HLOOKUP Function in Excel**

Let’s get introduced to our dataset first. The chart or the table below represents the number of orders of specific foods on some consecutive dates. **Column H** shows the number of canceled items or orders on those dates. By using **the HLOOKUP** **function**, we’ll find out how many fried chickens were sold on 7 August 2021 in that restaurant.

**📌**** Steps:**

➤ Select the output **Cell C15** and type:

`=HLOOKUP(C14,B4:H11,6,FALSE)`

➤ Press **Enter** and the function will return with 132. So, a total of 132 pieces of fried chickens was sold on the specified date.

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.

**2. Approximate Match with HLOOKUP Function in Excel**

Sometimes, we may forget what we’re looking for in a large range of data. In that case, we can use **Approximate Match** by defining the 4th argument of the **HLOOKUP** function **TRUE**. In that case, if our search criterion coincides partially with the exact name we’re looking for, then the function will look for that exact data and then return to the output cell.

So, assuming that we’re now replacing the name of the food item **‘Chicken’** with **‘Kitchen’** to see if our function works accordingly, as ‘Chicken’ and ‘Kitchen’ sound almost similar.

**📌**** Steps:**

➤ In the output **Cell C15**, the related formula will be:

`=HLOOKUP(C14,B4:H11,6,TRUE)`

➤ Press **Enter, **and you’ll get the resultant value of 132.

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 also input some other food names or partially matched names present in the table and see what the function returns with.

**3. Use of HLOOKUP with MATCH Function in Excel**

If you don’t want to define the row number every time inside **the HLOOKUP function**, rather you want to change the date or a value in **Cell C13** and find the instant result, then you have to use the **MATCH** function to define the row number of that data present in the corresponding cell.

For example, **Cells C13 and C14 **show the date and the food item, respectively. So, we want to find out how many sliders were sold on that specified date in **Cell C13**.

**📌**** Steps:**

➤ Select **Cell C15 **and type:

`=HLOOKUP(C14,B4:H11,MATCH(C13,B4:B11,0),FALSE)`

➤ Press** Enter**, and you’ll get the resultant value at once.

Now, every time you modify the date and name of the food item in **Cells C13 and C14**, the output will update accordingly in **Cell C15.**

**4. Defining Named Range for Table Array inside the HLOOKUP Function**

By defining the name of the table or the chart, we can replace the range of cells or array with the named range in the 2nd argument of the HLOOKUP function.

**📌**** Step 1:**

➤ Select the entire table and go to the **Name Box** on the left top.

➤ Edit the name and, for example, we can define the table with **Food_orders** or anything you prefer.

**📌**** Step 2:**

➤ Now select the output **Cell C15** where we’ll extract the number of sliders sold on 8 August 2021 and type there:

`=HLOOKUP(C14,Food_orders,MATCH(C13,B4:B11,0),FALSE)`

➤ Press **Enter **and the resultant value will be shown right away. So, this is how we can define a named range inside the **HLOOKUP** function for the** table_array** argument.

**5. Inserting Wildcard Character inside HLOOKUP Function to Search for Approximate Match**

By using a wildcard character- **Asterisk(*) **before and after a text, we can look for the exact word containing that text inside. It’s a sort of alternative to the Approximate Match that was discussed in the second method but the use of wildcard character- Asterisk will go for the exact search with more precision.

Let’s assume, we want to find out the number of orders for a food item containing the text- **‘Lime’** on **7 August, 2021.**

**📌**** Steps:**

➤ In the output **Cell C15,** the related formula with the wildcard character will be:

`=HLOOKUP("*Lime*",B4:H11,6,FALSE)`

➤ After pressing** Enter**, you’ll find the exact data extracted from the table.

**6. Cell Reference from Another Worksheet with HLOOKUP Function**

We can use the **HLOOKUP **function to extract data from other Excel worksheets too. For example, we have a table like the picture below in** Sheet1**.

In **Sheet 2,** we’ll extract the data from **Sheet1**. Assuming, we’re going to find out the number of orders of the food item **Sliders** on 6 August 2021.

**📌**** Steps:**

➤ Select **Cell C6** in **Sheet2** and type:

`=HLOOKUP(C4,Sheet1!B4:H11,MATCH(C2,Sheet1!B4:B11,0),FALSE)`

➤ Press **Enter** and the function will return the value 32 from the table present in **Sheet1**.

**7. Extracting Multiple Values with HLOOKUP Function in Excel**

By inserting an array containing the row numbers of the table inside the **HLOOKUP **function, we can pull out multiple data based on specific criteria.

For example, we’re going to find the number of the food item – Limeade sold on successive 5 days beginning from the date of **3 August, 2021. **

**📌**** Steps:**

➤ In the output **Cell C17**, the related formula will be:

`=HLOOKUP(C13,B4:H11,{2,3,4,5,6},FALSE)`

➤ After pressing **Enter**, the resultant values will return as an array in a row.

**8. Using IFERROR with HLOOKUP to Modify Error Message If Found**

When **the HLOOKUP function** can’t find a defined lookup value in the table, it’ll return a **Value Not Available (#N/A)** error. We can modify this error message by replacing it with **“Not Found”** by using **the IFERROR function** outside

**IFERROR **function returns **value_if_error** if the expression is an error and the value of the expression itself otherwise. The generic formula of this function is:

**=IFERROR(value, value_if_error)**

In our dataset, we’ll now find out the number of sandwiches sold on 7 August 2021. If the item is absent in the table, the function will return a **“Not Found”** message.

**📌**** Steps:**

➤ Select the output **Cell C15 **and type:

`=IFERROR(HLOOKUP(C14,B4:H11,6,FALSE),"Not Found")`

➤ Press **Enter** and you’ll get the resultant value at once based on the lookup criteria and defined message.

**💡**** Things to Keep in Mind**

🔺 **HLOOKUP** function only looks for the data based on the selected row(s) and if you need to extract data from the selected column(s), you have to use **the VLOOKUP function**.

🔺 **HLOOKUP** function searches for the value in the first row. So if your data is present in another row, make sure that you’re going to rearrange the table and keep the criteria in the first row.

🔺 **HLOOKUP** function is not case-sensitive. So it means the function will consider ALEX and alex identical.

🔺 As the 4th argument(**range_lookup**) of the HLOOKUP function is optional if you don’t mention the value of this argument, the function will take it as **TRUE** which represents the **Approximate Match**.

**Download Practice Workbook**

You can download the Excel workbook that we’ve used to prepare this article.

**Conclusion**

I hope all the methods mentioned above to use the HLOOKUP function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.

**<< Go Back to Excel Functions | Learn Excel**