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