How to Use Hlookup Function in Excel

HLOOKUP function is responsible for horizontal data retrieval. You will learn how to use a HLOOKUP function in Excel.

Hlookup function syntax

The HLOOKUP function’s syntax is similar to that of VLOOKUP, but with a horizontal data retrieval twist. Its basic form is as follows:

=HLOOKUP(reference, array, row_number, row)

In simple terms it can be assumed that the individual components of this function:

=VLOOKUP(what,where,in which row,true/false)

The last part of the formula is very important:

  • True is an approximate match,
  • False is the exact value.

I prepared a table of sales.

HLOOKUP example

You’ll learn from the above table, as HLOOKUP works.

With the name of the employee when you appear all the data on it that contains the table. At the beginning of the selection of employees could use the drop-down list.

HLOOKUP drop-down list

In the sales, type the formula:

=HLOOKUP(C7,C3:H5,2,FALSE)

HLOOKUP formula

Drag the formula in the City field. In this way, HLOOKUP searches for data from the table and selecting the employee name appears in the sale and the city.

Let’s analyze some more business examples on how to use HLOOKUP function in Excel.

Hlookup function examples

Find Price Horizontally with HLOOKUP

Imagine you have a horizontal list of product prices, and you need to find the price of a motorcycle.

See also  How to use xor function in Excel

Look for Price Horizontally with HLOOKUP

Locate Price Change Over Time

In a dynamic pricing scenario, you want to discover how much customers paid for a car after a price change.

Finding Price Vertically with HLOOKUP

Discover Email Address

In a list of employees or customers with their details scattered horizontally, you can find an email address using HLOOKUP.

HLOOKUP Finds Email Address

Determine Customer Debt

You have a list of customers with outstanding debts, but you need to find out how much Charles owes. HLOOKUP to the rescue:

Locating how Much a Customer Owns

Verify Payment of $500

You’re uncertain if a customer paid $500, and you need to confirm this. HLOOKUP can quickly locate this information:

Did Anybody Paid in 500

Handling Name Errors

If you encounter a name error, it’s likely due to missing quotation marks around the name. Simply add quotes for a quick fix:

Dealing With the Name Error

Dealing with N/A Errors

N/A errors may appear when the sought-after value isn’t in the table’s first row. A simple workaround is to ensure the value exists in the table:

How to Deal with NA Error

Retrieve Employees Phone Numbers

You need to find an employee’s phone number from a list. HLOOKUP makes it a breeze:

Locating Employees Telephone Number

Locate Employee Address

To send a paycheck to an employee on vacation, use HLOOKUP to retrieve the address:

Finding Address to Employee

Confirming Payments

If a customer claims to have overpaid, use HLOOKUP to verify the payment:

Confirming Payment

These examples showcase its versatility in various real-world scenarios, making it an essential skill for Excel users dealing with diverse datasets. However VLOOKUP and XLOOKUP are powerful as well.