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