Excel HLOOKUP Function
The Excel HLOOKUP function searches for a specific value in the first row of the selected range (table) and returns a value that resides in the same column as the lookup value from a specific row
Example: Excel HLOOKUP Function
|
=HLOOKUP(502,$C$4:$E$7,2,FALSE)
|
Result in cell E10 ($1.50) - searches for the exact value of 502 in range ($C$4:$E$4) and returns the value that resides in the same column as the lookup value from row 2 of the selected range.
|
|
=HLOOKUP(400,$C$4:$E$7,3,TRUE)
|
Result in cell E11 ($6.50) - searches for the approximate value of 400 in range ($C$4:$E$4) and returns the value that resides in the same column as the lookup value from row 3 of the selected range.
|
|
=HLOOKUP(345,$C$4:$E$7,4,FALSE)
|
Result in cell E12 ($4.70) - searches for the approximate value of 345 in range ($C$4:$E$4) and returns the value that resides in the same column as the lookup value from row 4 of the selected range.
|
METHOD 2. Excel HLOOKUP Function using links
EXCEL
|
=HLOOKUP(B10,$C$4:$E$7,C10,D10)
|
Result in cell E10 ($1.50) - searches for the exact value of 502 in range ($C$4:$E$4) and returns the value that resides in the same column as the lookup value from row 2 of the selected range.
|
|
=HLOOKUP(B11,$C$4:$E$7,C11,D11)
|
Result in cell E11 ($6.50) - searches for the approximate value of 400 in range ($C$4:$E$4) and returns the value that resides in the same column as the lookup value from row 3 of the selected range.
|
|
=HLOOKUP(B12,$C$4:$E$7,C12,D12)
|
Result in cell E12 ($4.70) - searches for the approximate value of 345 in range ($C$4:$E$4) and returns the value that resides in the same column as the lookup value from row 4 of the selected range.
|
METHOD 3. Excel HLOOKUP function using the Excel built-in function library with hardcoded values
EXCEL
| =HLOOKUP(502,$C$4:$E$7,2,FALSE) Note: in this example we are populating all of the HLOOKUP function arguments using hardcoded values. |
 |
METHOD 4. Excel HLOOKUP function using the Excel built-in function library with links
EXCEL
| =HLOOKUP(B10,$C$4:$E$7,C10,D10) Note: in this example we are populating all of the HLOOKUP function arguments using links. |
 |
Dim ws As Worksheet
ws.Range("E10").Value = WorksheetFunction.HLookup(502, ws.Range("$C$4:$E$7"), 2, False)
ws.Range("E11").Value = WorksheetFunction.HLookup(400, ws.Range("$C$4:$E$7"), 3, True)
ws.Range("E12").Value = WorksheetFunction.HLookup(345, ws.Range("$C$4:$E$7"), 4, False)
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named HLOOKUP.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E10"), ("E11") and ("E12") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
METHOD 2. Excel HLOOKUP function using VBA with links
VBA
Dim ws As Worksheet
ws.Range("E10").Value = WorksheetFunction.HLookup(ws.Range("B10"), ws.Range("$C$4:$E$7"), ws.Range("C10"), ws.Range("D10"))
ws.Range("E11").Value = WorksheetFunction.HLookup(ws.Range("B11"), ws.Range("$C$4:$E$7"), ws.Range("C11"), ws.Range("D11"))
ws.Range("E12").Value = WorksheetFunction.HLookup(ws.Range("B12"), ws.Range("$C$4:$E$7"), ws.Range("C12"), ws.Range("D12"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named HLOOKUP.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("E10"), ("E11") and ("E12") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
The Excel HLOOKUP function searches for a specific value in the first row of the selected range (table) and returns a value that resides in the same column as the lookup value from a specific row.
=HLOOKUP(lookup_value, table, row_index_number, [range_lookup])
lookup_value: (Required) The value you want to lookup in the first row from the selected range (table).
table: (Required) A table of data from which to lookup the value.
row_index_number: (Required) A row number in the table from which to source the data.
range_lookup: (Optional) A choice of TRUE or FALSE. Selecting TRUE will lookup an approximate match. Selecting FALSE will lookup an exact match.
ADDITIONAL NOTES
Note 1: The default range_lookup argument is TRUE (approximate match).
Note 2: If you have selected TRUE (approximate match) as the range_lookup value, the HLOOKUP function will initially search for the exact match. If the function cannot find an exact match the function will return the next smaller value. However, if your lookup values are not sorted in an ascending order this may return an incorrect value.
Note 3: The HLOOKUP function will return any value.
Note 4: The row_index_number argument cannot be below 1. If you have entered a value of less than 1 the HLOOKUP function will return an error (#VALUE!).
Note 5: The row_index_number argument cannot be greater than the number of rows in the selected range (table). If you have entered a value that is greater than the number of rows in the table the HLOOKUP function will return an error (#REF!).