HLOOKUP Function in Excel

HLOOKUP Function Glossary

• What is HLOOKUP?
• HLOOKUP Example
• Exact Match vs Approximate Match (False vs True)
• HLOOKUP from another Workbook or Worksheet

What is the HLOOKUP Function in Excel?

The HLOOKUP Function in Excel looks up and retrieves data from a specific row in a table. Inverse to VLOOKUP, HLOOKUP stands for “Horizontal lookup.” Like VLOOKUP, HLOOKUP utilizes Exact and Approximate matching. Though not as popular, the VLOOKUP Function in Excel is a powerful tool that can be used to streamline many spreadsheets.

Syntax

 =HLOOKUP (value, table, row_index, [range_lookup])  

Explanation

• value – Value from first column of a table.
• table – The table from which to obtain a value. We must define this table
• row_index – The row from the table to obtain a value.
• range_lookup – TRUE = Approximate Match (default). FALSE = Exact Match.

HLOOKUP Function Example

Let’s look at the following example using the HLOOKUP function. We have sales data broken up in quarters for 2019. We want Excel to look for the result in row 4 under “Q3 2019.” The syntax is explained below.

=HLOOKUP("Q3 2019",A1:E5,4,TRUE)
value - "Q3 2019" - Parenthesis are required for text.
table -  A1:E5 
row_index - 4
range_lookup - FALSE - Exact Match

TRUE vs FALSE – Approximate Match vs Exact Match

For a more detailed explanation between TRUE (Match) and FALSE (Exact), please refer to the VLOOKUP Tutorial.

Approximate Match

Approximate is nearly correct, close, but not exact. This is the distinguishing difference between Approximate Match and Exact Match.

Exact Match

By using range_lookup set to FALSE, Excel is told to look for an Exact match (FALSE). This is the most commonly used setting.

HLOOKUP from another Workbook or Worksheet

Let’s review how to link to another worksheet using HLOOKUP. We are linking to the worksheet “HLOOKUP from Another Sheet” tab as shown below. The demo file at the beginning of this tutorial contains this table.

As you can see, you can easily select the other worksheet. You can select another Workbook as long as the other workbook is open while selecting. Excel will automatically complete the path.

=HLOOKUP("Q4 2019",'HLOOKUP from Another Sheet'!A1:E6,3,FALSE)
value - "Q4 2019" - Parenthesis are required for text.
table -   HLOOKUP from Another Sheet'!A1:E6  
row_index - 3
range_lookup - FALSE - Exact Match 

If you have any other questions regarding HLOOKUP, please let us know.