Site icon Best Excel Tutorial

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:

I prepared a table of sales.

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.

In the sales, type the formula:

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

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.

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.

Discover Email Address

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

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:

Verify Payment of $500

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

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 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:

Retrieve Employees Phone Numbers

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

Locate Employee Address

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

Confirming Payments

If a customer claims to have overpaid, use HLOOKUP to verify the 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.

Exit mobile version