# HLOOKUP Function in Excel

🌟 Part 1: Introduce 🌟

🔔 Definition: The HLOOKUP function in Microsoft Excel is a powerful lookup function for “Horizontal Lookup.” It is used to search for a value in the topmost row of a table and return the corresponding value from a specified row below it. HLOOKUP is ideal for situations where data is organized in rows, and you must extract information based on a specific criterion.

🔔 Purpose: The primary purpose of the HLOOKUP function is to look up and retrieve data from a table in a horizontal direction. It helps users quickly find and display information based on a specific search key.

🔔 Syntax & Arguments: The syntax of the HLOOKUP function is as follows:

syntax
```=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) ```

Arguments:

• `lookup_value`: The value to be found in the first row of the table_array.
• `table_array`: The range of cells that contains the data to be searched. The first row of this range should have the lookup_value.
• `row_index_num`: The row number in the table_array from which to return the result. The first row is 1; the second is 2, and so on.
• `range_lookup` (optional): This argument specifies whether to find an exact or approximate match. Use `TRUE` it for an imprecise game or `FALSE` an exact match. If omitted, the default value is `TRUE`.

🔔 Return Value: The HLOOKUP function returns the value corresponding to the given lookup_value from the specified row in the table_array.

🔔 Remarks:

• The HLOOKUP function only works with data organized in rows, whereas the VLOOKUP function works with data collected in columns.
• It is crucial to ensure that the lookup_value exists in the topmost row of the table_array. Otherwise, the function may return unexpected results or errors.
• For exact matches, always use `FALSE` the range_lookup argument. For approximate games, use `TRUE`.

🌟 Part 2: Examples 🌟

Example 1: Employee Salary Table (Sheet: “EmployeeSalary”)

ABC
1Employee IDNameSalary
2101John\$60,000
3102Sarah\$72,000
4103Michael\$65,000

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2102=HLOOKUP(A2, EmployeeSalary!\$A\$1:\$C\$4, 3, FALSE)
3103=HLOOKUP(A3, EmployeeSalary!\$A\$1:\$C\$4, 3, FALSE)
4101=HLOOKUP(A4, EmployeeSalary!\$A\$1:\$C\$4, 3, FALSE)

Explanation: In this example, we want to find an employee’s salary using the HLOOKUP function. The formula in cell B2 would be “=HLOOKUP(A2, EmployeeSalary!\$A\$1:\$C\$4, 3, FALSE)”. The function searches for the “Employee ID” (102) in the topmost row (A1:C1) of the “EmployeeSalary” sheet and returns the corresponding value from the third row (Salary), which is \$72,000.

Example 2: Product Sales Table (Sheet: “ProductSales”)

ABC
1Product IDProduct NameSales
2101Laptop\$25,000
3102Smartphone\$32,000
4103Tablet\$18,000

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2102=HLOOKUP(A2, ProductSales!\$A\$1:\$C\$4, 3, FALSE)
3101=HLOOKUP(A3, ProductSales!\$A\$1:\$C\$4, 3, FALSE)
4103=HLOOKUP(A4, ProductSales!\$A\$1:\$C\$4, 3, FALSE)

Explanation: In this example, we want to find the sales of a product using the HLOOKUP function. The formula in cell B2 would be “=HLOOKUP(A2, ProductSales!\$A\$1:\$C\$4, 3, FALSE)”. The function searches for the “Product ID” (102) in the topmost row (A1:C1) of the “ProductSales” sheet and returns the corresponding value from the third row (Sales), which is \$32,000.

Example 3: Student Exam Scores Table (Sheet: “ExamScores”)

ABC
1Student IDNameExam Score
2101John85
3102Sarah92
4103Michael78

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2102=HLOOKUP(A2, ExamScores!\$A\$1:\$C\$4, 3, FALSE)
3101=HLOOKUP(A3, ExamScores!\$A\$1:\$C\$4, 3, FALSE)
4103=HLOOKUP(A4, ExamScores!\$A\$1:\$C\$4, 3, FALSE)

Explanation: In this example, we want to find a student’s exam score using the HLOOKUP function. The formula in cell B2 would be “=HLOOKUP(A2, ExamScores!\$A\$1:\$C\$4, 3, FALSE)”. The function searches for the “Student ID” (102) in the topmost row (A1:C1) of the “ExamScores” sheet and returns the corresponding value from the third row (Exam Score), which is 92.

🌟 Example 4: Product Inventory Table (Sheet: “ProductInventory”)

ABC
1Product IDProduct NameQuantity
2101Laptop15
3102Smartphone20
4103Tablet10

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2102=IF(HLOOKUP(A2, ProductInventory!\$A\$1:\$C\$4, 3, FALSE) > 10, “In Stock”, “Out of Stock”)
3101=IF(HLOOKUP(A3, ProductInventory!\$A\$1:\$C\$4, 3, FALSE) > 10, “In Stock”, “Out of Stock”)
4103=IF(HLOOKUP(A4, ProductInventory!\$A\$1:\$C\$4, 3, FALSE) > 10, “In Stock”, “Out of Stock”)

Explanation: In this example, we want to check if a product is “In Stock” or “Out of Stock” based on the quantity using the HLOOKUP function nested with the IF function. The formula in cell B2 would be “=IF(HLOOKUP(A2, ProductInventory!\$A\$1:\$C\$4, 3, FALSE) > 10, “In Stock”, “Out of Stock”)”. The HLOOKUP function searches for the “Product ID” (102) in the topmost row (A1:C1) of the “ProductInventory” sheet and returns the corresponding value from the third row (Quantity), which is 20. The IF function then checks if the quantity exceeds 10 and returns “In Stock” if true and “Out of Stock” if false.

🌟 Example 5: Sales Data Table (Sheet: “SalesData”)

ABC
1MonthProductSales
2JanLaptop\$25,000
3FebSmartphone\$32,000
4MarTablet\$18,000

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2Feb=HLOOKUP(A2, SalesData!\$A\$1:\$C\$4, 3, FALSE) + HLOOKUP(A2, SalesData!\$A\$1:\$C\$4, 3, FALSE)
3Jan=HLOOKUP(A3, SalesData!\$A\$1:\$C\$4, 3, FALSE) + HLOOKUP(A3, SalesData!\$A\$1:\$C\$4, 3, FALSE)
4Mar=HLOOKUP(A4, SalesData!\$A\$1:\$C\$4, 3, FALSE) + HLOOKUP(A4, SalesData!\$A\$1:\$C\$4, 3, FALSE)

Explanation: In this example, we want to calculate the total sales for each month using the HLOOKUP function nested with the SUM function. The formula in cell B2 would be “=HLOOKUP(A2, SalesData!\$A\$1:\$C\$4, 3, FALSE) + HLOOKUP(A2, SalesData!\$A\$1:\$C\$4, 3, FALSE)”. The HLOOKUP function searches for “Feb” in the topmost row (A1:C1) of the “SalesData” sheet and returns the corresponding value from the third row (Sales), which is \$32,000. The SUM function then adds this value to itself, resulting in the total sales for February, which is \$64,000.

🌟 Example 6: Exam Scores Table (Sheet: “ExamScores”)

ABC
1SubjectMax ScoreAverage
2Math10085
3Science10092
4English10078

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2Science=HLOOKUP(A2, ExamScores!\$A\$1:\$C\$4, 3, FALSE) / HLOOKUP(A2, ExamScores!\$A\$1:\$C\$4, 2, FALSE)
3Math=HLOOKUP(A3, ExamScores!\$A\$1:\$C\$4, 3, FALSE) / HLOOKUP(A3, ExamScores!\$A\$1:\$C\$4, 2, FALSE)
4English=HLOOKUP(A4, ExamScores!\$A\$1:\$C\$4, 3, FALSE) / HLOOKUP(A4, ExamScores!\$A\$1:\$C\$4, 2, FALSE)

Explanation: In this example, we want to calculate the percentage score for each subject using the HLOOKUP function nested with the division operation. The formula in cell B2 would be “=HLOOKUP(A2, ExamScores!\$A\$1:\$C\$4, 3, FALSE) / HLOOKUP(A2, ExamScores!\$A\$1:\$C\$4, 2, FALSE)”. The HLOOKUP function searches for “Science” in the topmost row (A1:C1) of the “ExamScores” sheet and returns the corresponding value from the third row (Average), which is 92. It then divides this value by the value returned from the second HLOOKUP function fetching the maximum Science score (100). The result is the percentage score for Science, which is 92%.

🌟 Example 7: Employee Performance Table (Sheet: “EmployeePerformance”)

ABC
1Employee IDNameRating
2101John4.5
3102Sarah4.2
4103Michael4.8

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2102=IF(HLOOKUP(A2, EmployeePerformance!\$A\$1:\$C\$4, 3, FALSE) > 4.5, “Excellent”, “Good”)
3101=IF(HLOOKUP(A3, EmployeePerformance!\$A\$1:\$C\$4, 3, FALSE) > 4.5, “Excellent”, “Good”)
4103=IF(HLOOKUP(A4, EmployeePerformance!\$A\$1:\$C\$4, 3, FALSE) > 4.5, “Excellent”, “Good”)

Explanation: In this example, we want to categorize employees as “Excellent” or “Good” based on their performance rating using the HLOOKUP function nested with the IF function. The formula in cell B2 would be “=IF(HLOOKUP(A2, EmployeePerformance!\$A\$1:\$C\$4, 3, FALSE) > 4.5, “Excellent”, “Good”)”. The HLOOKUP function searches for the “Employee ID” (102) in the topmost row (A1:C1) of the “EmployeePerformance” sheet and returns the corresponding value from the third row (Rating), which is 4.2. The IF function then checks if the rating exceeds 4.5 and returns “Excellent” if true and “Good” if false.

🌟 Example 8: Sales Targets Table (Sheet: “SalesTargets”)

ABC
1MonthProductTarget
2JanLaptop\$30,000
3FebSmartphone\$40,000
4MarTablet\$25,000

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2Feb=IF(HLOOKUP(A2, SalesTargets!\$A\$1:\$C\$4, 3, FALSE) >= 35000, “Met Target”, “Below Target”)
3Jan=IF(HLOOKUP(A3, SalesTargets!\$A\$1:\$C\$4, 3, FALSE) >= 35000, “Met Target”, “Below Target”)
4Mar=IF(HLOOKUP(A4, SalesTargets!\$A\$1:\$C\$4, 3, FALSE) >= 35000, “Met Target”, “Below Target”)

Explanation: In this example, we want to determine whether sales for a product have met the target using the HLOOKUP function nested with the IF function. The formula in cell B2 would be “=IF(HLOOKUP(A2, SalesTargets!\$A\$1:\$C\$4, 3, FALSE) >= 35000, “Met Target”, “Below Target”)”. The HLOOKUP function searches for “Feb” in the topmost row (A1:C1) of the “SalesTargets” sheet and returns the corresponding value from the third row (Target), which is \$40,000. The IF function then checks if the sales have met the target (greater than or equal to \$35,000) and returns “Met Target” if true and “Below Target” if false.

🌟 Example 9: Expenses Table (Sheet: “Expenses”)

ABC
1CategoryAmount SpentBudget
2Food\$800\$1000
3Transport\$250\$300
4Utilities\$400\$500

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2Transport=IF(HLOOKUP(A2, Expenses!\$A\$1:\$C\$4, 2, FALSE) > HLOOKUP(A2, Expenses!\$A\$1:\$C\$4, 3, FALSE), “Under Budget”, “Over Budget”)
3Utilities=IF(HLOOKUP(A3, Expenses!\$A\$1:\$C\$4, 2, FALSE) > HLOOKUP(A3, Expenses!\$A\$1:\$C\$4, 3, FALSE), “Under Budget”, “Over Budget”)
4Food=IF(HLOOKUP(A4, Expenses!\$A\$1:\$C\$4, 2, FALSE) > HLOOKUP(A4, Expenses!\$A\$1:\$C\$4, 3, FALSE), “Under Budget”, “Over Budget”)

Explanation: In this example, we want to check if expenses for each category are “Under Budget” or “Over Budget” using the HLOOKUP function nested with the IF function. The formula in cell B2 would be “=IF(HLOOKUP(A2, Expenses!\$A\$1:\$C\$4, 2, FALSE) > HLOOKUP(A2, Expenses!\$A\$1:\$C\$4, 3, FALSE), “Under Budget”, “Over Budget”)”. The HLOOKUP function searches for “Transport” in the topmost row (A1:C1) of the “Expenses” sheet and returns the corresponding value from the second row (Amount Spent), which is \$250. It then compares this value to the value from the third row (Budget), which is \$300. The IF function then checks if the amount spent exceeds the budget and returns “Under Budget” if true and “Over Budget” if false.

🌟 Example 10: Employee Bonus Table (Sheet: “EmployeeBonus”)

ABC
1Employee IDNameBonus
2101John\$500
3102Sarah\$800
4103Michael\$600

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2103=HLOOKUP(A2, EmployeeBonus!\$A\$1:\$C\$4, 3, FALSE) * 0.1
3102=HLOOKUP(A3, EmployeeBonus!\$A\$1:\$C\$4, 3, FALSE) * 0.1
4101=HLOOKUP(A4, EmployeeBonus!\$A\$1:\$C\$4, 3, FALSE) * 0.1

Explanation: In this example, we want to calculate the bonus for each employee using the HLOOKUP function nested with the multiplication operation. The formula in cell B2 would be “=HLOOKUP(A2, EmployeeBonus!\$A\$1:\$C\$4, 3, FALSE) * 0.1”. The HLOOKUP function searches for “103” in the topmost row (A1:C1) of the “EmployeeBonus” sheet and returns the corresponding value from the third row (Bonus), which is \$600. The multiplication operation then calculates 10% of the bonus, resulting in a bonus amount of \$60.

🌟 Example 11: Stock Prices Table (Sheet: “StockPrices”)

ABC
1StockJan PriceFeb Price
2AAPL\$150\$160
3GOOG\$2000\$2100
4MSFT\$250\$260

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2AAPL=HLOOKUP(A2, StockPrices!\$A\$1:\$C\$4, 3, FALSE) – HLOOKUP(A2, StockPrices!\$A\$1:\$C\$4, 2, FALSE)
3GOOG=HLOOKUP(A3, StockPrices!\$A\$1:\$C\$4, 3, FALSE) – HLOOKUP(A3, StockPrices!\$A\$1:\$C\$4, 2, FALSE)
4MSFT=HLOOKUP(A4, StockPrices!\$A\$1:\$C\$4, 3, FALSE) – HLOOKUP(A4, StockPrices!\$A\$1:\$C\$4, 2, FALSE)

Explanation: In this example, we want to calculate the price change for each stock using the HLOOKUP function nested with the subtraction operation. The formula in cell B2 would be “=HLOOKUP(A2, StockPrices!\$A\$1:\$C\$4, 3, FALSE) – HLOOKUP(A2, StockPrices!\$A\$1:\$C\$4, 2, FALSE)”. The HLOOKUP function searches for “AAPL” in the topmost row (A1:C1) of the “StockPrices” sheet and returns the corresponding value from the third row (Feb Price), which is \$160. It then subtracts the value returned from the second HLOOKUP function that fetches the January price for AAPL (\$150). The result is the price change for AAPL, which is \$10.

🌟 Example 12: Temperature Data Table (Sheet: “TemperatureData”)

ABC
1CityJan TempFeb Temp
2New York30°C32°C
3Los Angeles22°C24°C
4Chicago15°C18°C

Table 2 (HLOOKUP Result) – Sheet: “ResultsSheet”

AB
1Search ValueResult
2Los Angeles=IF(HLOOKUP(A2, TemperatureData!\$A\$1:\$C\$4, 3, FALSE) > 25, “Hot”, “Cold”)
3Chicago=IF(HLOOKUP(A3, TemperatureData!\$A\$1:\$C\$4, 3, FALSE) > 25, “Hot”, “Cold”)
4New York=IF(HLOOKUP(A4, TemperatureData!\$A\$1:\$C\$4, 3, FALSE) > 25, “Hot”, “Cold”)

Explanation: In this example, we want to categorize cities as “Hot” or “Cold” based on their average temperatures using the HLOOKUP function nested with the IF function. The formula in cell B2 would be “=IF(HLOOKUP(A2, TemperatureData!\$A\$1:\$C\$4, 3, FALSE) > 25, “Hot”, “Cold”)”. The HLOOKUP function searches for “Los Angeles” in the topmost row (A1:C1) of the “TemperatureData” sheet and returns the corresponding value from the third row (Feb Temp), which is 24°C. The IF function then checks if the average temperature exceeds 25°C and returns “Hot” if true and “Cold” if false.

🌟 Part 3: Tips and Tricks 🌟

• Ensure that the lookup_value exists in the first row of the table_array; otherwise, HLOOKUP may return unexpected results.
• Always use `FALSE` the range_lookup argument for an exact match to avoid errors due to approximate conflicts.
• To prevent mistakes when the lookup_value is not found, consider using the IFERROR function to display a custom message.
• Organize your data structure to make HLOOKUP and other lookup functions more effective and efficient.
• Use named ranges to make your formulas more readable and easier to maintain.
• Regularly review and update your tables to ensure the accuracy of your HLOOKUP results.