Excel HLOOKUP() Function

Excel HLOOKUP() Function

The HLOOKUP() function in excel is used to search a value in the topmost row of a table and then return a corresponding value in the same column for the specified row. This function is similar to VLOOKUP. In a VLOOKUP, we look at columns, whereas in HLOOKUP, we look at rows.

Syntax

HLOOKUP(LookupValue, Table_Array, Row_Index_Num, [Range_Lookup])

Parameter

LookupValue(required)- This parameter represents the value which needs to be looked for in a particular table.

Table_Array (required)- This parameter refers to the table in which we need to check for this value to be present and from which we want to retrieve data. By pressing the F2 key when you have selected your table, you fix the range. So when you copy the formula from one cell to another, the range does not update automatically.

Index_Num(required)- If the value is present in the table, then which column consists of the value we want the function to return

Range_Lookup(optional)- This parameter takes a Boolean value - TRUE for approximate match and FALSE (which is the default value) for an exact match of the value we are searching for in a table.

Category

Lookup & Reference

Return

The HLOOKUP function looks for a value in the first row of a table and returns a value from the same column in that table.

Example 1

Objective: Below in the first table, we have a sales commission for different cars. Use HLOOKUP() to get the amount each employee will get based on the car they sold.

Car Type Mercedes BMW Audi VW Ferrari
Commission $500 $450 $550 $200 $1,000
Sold By Maker Commission Formula
Yusuf Ferrari £1,000  =HLOOKUP(B29,$B$25:$F$26,2,FALSE)
Alen BMW £450  =HLOOKUP(B30,$B$25:$F$26,2,FALSE)
Terry Audi £550  =HLOOKUP(B31,$B$25:$F$26,2,FALSE)
David VW £200  =HLOOKUP(B32,$B$25:$F$26,2,FALSE)
Kim Mercedes £500  =HLOOKUP(B33,$B$25:$F$26,2,FALSE)
Bin Ferrari £1,000  =HLOOKUP(B34,$B$25:$F$26,2,FALSE)
Arnav BMW £450  =HLOOKUP(B35,$B$25:$F$26,2,FALSE)
Sohaib Mercedes £500  =HLOOKUP(B36,$B$25:$F$26,2,FALSE)

Example 2

Objective: How much % target achieved by 'Value 4’ in Eoo4

Name Value 1 Value 2 Value 3 Value 4
E001 99% 53% 74% 79%
E002 52% 97% 46% 88%
E003 94% 128% 93% 31%
E004 59% 141% 121% 98%
E005 44% 70% 83% 64%
E006 86% 89% 15% 91%
Lookup Value Product 3
% Target Achieved 121% =HLOOKUP(C98,B88:F94,5,0)



ADVERTISEMENT
ADVERTISEMENT