How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value on the same column in a row you specify.
HLOOKUP stands for horizontal lookup and your data (table_array) must be organized into records, a record on each row.
To search records excel must look horizontally in the top row of the data array. VLOOKUP stands for vertical lookup but that is another post.
Formula in cell E2:
The HLOOKUP function in cell E2 searches the top row of cell range B5:E14 for value "Item" and it is found in the second column.
The third argument tells the function which row to fetch the value from, in this case it is four.
The intersection of the second column and the fourth row is value DD and that is the returning value in cell E2.
Excel Function Syntax
HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Arguments
Lookup_value | Required. The first argument is the lookup value. This is the value you want to find in the top row of the table array. |
Table_array | Required. This is a cell range or an array that you want to search. |
Row_index_num | Required. Here you specify which row you want fetch the value from. |
Range_lookup | Optional. You may select TRUE or FALSE here, TRUE tells the function to do an approximate match and this is the default value. |
Make sure the top row in the table array is sorted ascending. FALSE means exact match and this mode is probably what you will use the most. Take note that if you use the wrong argument here you may get incorrect results.
Table of Contents
- Overview
- Arguments
- Approximate match
- Horizontal and vertical lookup
- Horizontal and vertical lookup - INDEX + MATCH
- HLOOKUP - multiple tables
- VBA Example
- Download excel *.xlsm file
- Functions in this post
Approximate match
The HLOOKUP function allows you to search with an approximate match if you enter TRUE in the fourth argument. In the example below there are four different ranges, 0 and less than 100, 100 and less than 250, 250 and less than 500 and 500 and more.
The value 189 in cell C2 can't be found in the top row of C5:F6 so the next largest value that is less than lookup_value (189) is matched, in this case 100. The corresponding value is 12.1 and is returned in cell C3.
Horizontal and vertical lookup
The following formula lets you search both horizontally and vertically. The formula in cell E2 looks for Item in the top horizontal row and 141 in column "Invoice", the intersecting cell is C9 so the formula returns AA.
Formula in cell E2:
Horizontal and vertical lookup - INDEX + MATCH
You can also use INDEX and MATCH functions to get a value in a cross reference table. The formula below works just like the formula above.
Formula in cell E2:
This formula is actually easier to understand and remember than the HLOOKUP function, this is the method I prefer.
HLOOKUP - multiple tables
The formula below lets you also choose a table to search. The INDIRECT function lets you convert a cell value to a cell reference, however the INDIRECT function is volatile, so use it with care.
Volatile means it is recalculated every time the worksheet is recalculated. If you have many INDIRECT formulas, they may slow down your workbook.
Formula in cell J4:
HLOOKUP - VBA Example
The following macro lets you search a table using the parameters in cell C2 and C3. A messagebox appears and tells you the returning value.
VBA Code
Sub HLP() MsgBox Application.WorksheetFunction.HLookup(Range("C2"), Range("B5:E14"), Range("C3"), False) End Sub
Download excel function
Functions in 'Lookup and reference'
The HLOOKUP function function is one of many functions in the 'Lookup and reference' category.
How to use the ADDRESS function
The ADDRESS function returns the address of a specific cell, you need to provide a row and column number. Formula […]
The AREAS function returns the number of cell ranges and single cells in a reference. Formula in cell B3: =AREAS(E3:E4) […]
How to use the CHOOSE function
The CHOOSE function allows you to retrieve a value based on a number, cell range C3:C4 in the picture above […]
How to use the COLUMN function
The COLUMN function returns the column number of the top left cell of a cell reference. If the argument is not […]
How to use the COLUMNS function
The COLUMNS function allows you to calculate the number of columns in a cell range. The example above shows that cell […]
How to use the FILTER function
The FILTER function lets you extract values/rows based on a condition or criteria. It is in the Lookup and reference category […]
How to use the FORMULATEXT function
The FORMULATEXT function returns a formula as a text string. Formula in cell C3: =FORMULATEXT(B3) Excel Function Syntax FORMULATEXT(reference) Arguments […]
How to use the HLOOKUP function
The HLOOKUP function lets you search the top row in a data range for a value and return another value […]
How to use the HYPERLINK function
The HYPERLINK function allows you to build a link in a cell pointing to something else like a file, workbook, cell, […]
Gets a value in a specific cell range based on a row and column number.
How to use the INDIRECT function
The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference. […]
How to use the LOOKUP function
Finds a value in a sorted cell range and returns a value on the same row.
Identify the position of a value in an array.
How to use the OFFSET function
The OFFSET function returns a reference to a range that is a given number of rows and columns from a […]
The ROW function calculates the row number of a cell reference. Excel Function Syntax ROW(reference) Arguments reference Optional. A reference […]
The ROWS function allows you to calculate the number of rows in a cell range. The example above shows that cell […]
The SORT function lets you sort values from a cell range or array. It returns an array with a size […]
How to use the TRANSPOSE function
The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]
How to use the UNIQUE function
The UNIQUE function is a very versatile Excel function, it lets you extract both unique and unique distinct values and […]
How to use the VLOOKUP function
The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form