## How to use the HLOOKUP function

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

#### Overview

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.

#### Syntax

**HLOOKUP(**

*lookup_value, table_array, col_index_num, [range_lookup]*

**)**

#### Quick example

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.

Formula in cell E2:

#### Arguments

*Lookup_value* - 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 *- This is a cell range or an array that you want to search.

*Row_index_num *- Here you specify which row you want fetch the value from.

*Range_lookup *- 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.

#### 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 this post

**INDEX function explained**

INDEX(array,row_num,[column_num])

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MATCH function**

MATCH(lookup_value, lookup_array, [match_type])

Returns the relative position of an item in an array that matches a specified value in a specific order

**How to use the IFERROR function**

IFERROR(value, value_if_error)

Returns value_if_error if expression is an error and the value of expression itself otherwise.

### Category: Functions

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

This function lets you extract any number in a cell range based on sort rank.

Comments(12) Filed in category: Excel, Functions

Identify the position of a value in an array.

Comments(12) Filed in category: Excel, Functions

Microsoft Excel has two useful functions for generating numbers. The RAND function and RANDBETWEEN function. RAND() function returns a random […]

Comments(10) Filed in category: Excel, Functions

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

Comments(9) Filed in category: Excel, Functions

The Mod function returns the remainder after a number is divided by divisor. The Mod function is short for the Modulo […]

Comments(8) Filed in category: Excel, Functions, Mod, Quotient

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel, Functions

The transpose function allows you to convert a vertical range to a horizontal range, or vice versa. A vertical range […]

Comments(4) Filed in category: Excel, Functions, Transpose

The TEXTJOIN function is a new function for office 365 subscribers, it is like the CONCATENATE function on steroids or what the CONCATENATE function […]

Comments(4) Filed in category: Excel, Functions, Textjoin

MMULT(array1, array2) Returns the matrix product of two arrays, an array as the same number of rows as array1 and […]

Comments(4) Filed in category: Excel, Functions, Mmult