The HLOOKUP function searches for a value in the first row of a table and returns a value from the same column in a specified row. HLOOKUP is the horizontal equivalent of VLOOKUP, perfect for data organized in rows rather than columns.
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)The value to search for in the first row of the table. This can be a number, text, logical value, or cell reference.
The range of cells containing the data table. The first row contains the lookup values, and subsequent rows contain the return values.
The row number in the table from which to return a value. Row 1 is the lookup row, row 2 is the first data row.
TRUE for approximate match (default) or FALSE for exact match. Use FALSE for exact matches to avoid incorrect results.
Value from the specified row in the same column as the lookup value
Description: Searches horizontally in the first row and returns a value from the same column in a specified row
Look up value in horizontal table
Searches for "Feb" in the first row and returns the value from row 2 (200) in the same column.
Master the fundamentals of Excel HLOOKUP function
The HLOOKUP function is Excel's horizontal lookup tool that searches for a value in the first row of a table and returns a value from the same column in a specified row below. It's the horizontal equivalent of VLOOKUP and is ideal for data organized in rows, such as quarterly reports, monthly data, or transposed tables.
Searches horizontally in the first row
Returns value from specified row number
Use FALSE for exact matches (recommended)
Use TRUE for sorted data ranges
Function-specific parameters
Function-specific return type
Retrieve data from horizontal quarterly layouts
Work with data organized in rows instead of columns
Look up values in month-based horizontal tables
Find values based on column headers in first row
Exact matching required
Returns numeric position
Handles missing text gracefully
=HLOOKUP(lookup_value, table_array, row_index_num, range_lookup)Value to find in first row
Range containing lookup table
Row number to return value from (1 = lookup row)
TRUE for approximate, FALSE for exact (default TRUE)
Value from specified row in same column as lookup value
Description: Searches first row and returns value from specified row below
Horizontal lookup
Finds Feb in row 1, returns 200 from row 2
Using HLOOKUP in VBA
Sub HLOOKUPExample()
' Method 1: Using WorksheetFunction.HLookup
Dim result As Variant
result = Application.WorksheetFunction.HLookup("Feb", Range("A1:D2"), 2, False)
Range("E1").Value = result
' Method 2: With error handling
On Error Resume Next
result = Application.WorksheetFunction.HLookup("Mar", Range("A1:D2"), 2, False)
If Err.Number <> 0 Then
Range("E2").Value = "Not Found"
Err.Clear
Else
Range("E2").Value = result
End If
On Error GoTo 0
' Method 3: Loop through lookup values
Dim lookupValues As Variant
Dim i As Integer
lookupValues = Array("Jan", "Feb", "Mar", "Apr")
For i = LBound(lookupValues) To UBound(lookupValues)
Range("E" & i + 3).Value = Application.WorksheetFunction.HLookup( _
lookupValues(i), Range("A1:D2"), 2, False)
Next i
' Method 4: Dynamic table reference
Dim ws As Worksheet
Set ws = ActiveSheet
Dim tableRange As Range
Set tableRange = ws.Range("A1:D5")
result = Application.WorksheetFunction.HLookup("Q2", tableRange, 3, False)
ws.Range("F1").Value = result
End SubRetrieve quarterly data from horizontal layout
Look up monthly values in row-based tables
Work with transposed data structures
Find values using first row as headers
Lookup value not found in first row
=IFERROR(HLOOKUP(A1, B1:D2, 2, FALSE), "Not Found")Solution: Verify the lookup value exists in the first row. Check for typos, case sensitivity, and extra spaces. Use TRIM and exact matching (FALSE). Consider using IFERROR to handle #N/A gracefully.
HLOOKUP returns incorrect value
=HLOOKUP(A1, B1:D5, 3, FALSE)Solution: Verify row_index_num is correct. Remember row 1 is the lookup row, so row 2 is the first data row. Double-check the table range includes all necessary rows.
Approximate match returns wrong results
=HLOOKUP(Value, Table, RowNum, FALSE)Solution: For approximate match (TRUE), the first row must be sorted in ascending order. Use FALSE for exact matches unless you specifically need approximate matching with sorted data.
Slow performance with large horizontal tables
Use specific range: A1:D10 instead of 1:10Solution: Limit table_array size to only necessary rows. Avoid using entire rows (1:1) in large spreadsheets. Consider using INDEX/MATCH or XLOOKUP for better performance.