Purpose

Return value

Syntax

=HLOOKUP(lookup_value,table_array,row_index,[range_lookup])
  • lookup_value - The value to look up.
  • table_array - The table from which to retrieve data.
  • row_index - The row number from which to retrieve data.
  • range_lookup - [optional] A Boolean to indicate exact match or approximate match. Default = TRUE = approximate match.

Using the HLOOKUP function

The HLOOKUP function can locate and retrieve a value from data in a horizontal table . Like the “V” in VLOOKUP which stands for “vertical”, the “H” in HLOOKUP stands for “horizontal”. The lookup values must appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and wildcards (* ?) for finding partial matches.

HLOOKUP searches for a value in the first row of a table. When it finds a match, it retrieves a value at that column from the row given. Use HLOOKUP when lookup values are located in the first row of a table. Use VLOOKUP when lookup values are located in the first column of a table.

HLOOKUP takes four arguments . The first argument, called lookup_value , is the value to look up. The second argument, table_array , is a range that contains the lookup table. The third argument, row_index_num is the row number in the table from which to retrieve a value. In the example shown, HLOOKUP is used to look up values from row 2 (Level) and row 3 (Bonus) in the table. The fourth and final argument, range_lookup , controls matching. Use TRUE or 1 for an approximate match and FALSE or 0 for an exact match.

Example #1 - approximate match

In the example shown, the goal is to look up the correct Level and Bonus for the sales amounts in C5:C13. The lookup table is in H4:J6, which is the named range “table”. Note this is an approximate match scenario. For each amount in C5:C13, the goal is to find the best match, not an exact match. To lookup Level, the formula in cell D5, copied down, is:

=HLOOKUP(C5,table,2,1) // get level

To get Bonus, the formula in E5, copied down, is:

=HLOOKUP(C5,table,3,1) // get bonus

Notice the only difference between the two formulas is the row index number: Level comes from row 2 in the lookup table, while Bonus comes from row 3. The match mode has been set explicitly to approximate match by providing the last argument, range_lookup , as 1.

Example #2 - exact match

In the screen below, the goal is to look up the correct level for a numeric rating 1-4. In cell D5, the HLOOKUP formula, copied down, is:

=HLOOKUP(C5,table,2,FALSE) // exact match
HLOOKUP exact match example - 1

where table is the named range G4:J5. Notice the last argument, range_lookup is set to FALSE to require an exact match.

Notes

  • Range_lookup controls whether the lookup value needs to match exactly or not. The default is TRUE = allow non-exact match.
  • Set range_lookup to FALSE to require an exact match.
  • If range_lookup is omitted or TRUE, and no exact match is found, HLOOKUP will match the nearest value in the table that is still less than the lookup value . However, HLOOKUP will still match an exact value if one exists.
  • If range_lookup is TRUE , lookup values in the first row of the table must be sorted in ascending order. Otherwise, HLOOKUP may return an incorrect or unexpected value.
  • If range_lookup is FALSE (exact match), values in the first row of the lookup table do not need to be sorted.

Purpose

Return value

Syntax

=HYPERLINK(link_location,[friendly_name])
  • link_location - The path to the file or page to be opened.
  • friendly_name - [optional] The link text to display in a cell.

The HYPERLINK function creates a hyperlink to a given destination with a “friendly name”, which is simply the anchor text. You can use HYPERLINK to construct a clickable hyperlink with a formula. The HYPERLINK function can build links to other cells in a workbook, other sheets, named ranges, other workbooks, pages on the internet, or files on network servers. You can also use HYPERLINK to create email links.

The HYPERLINK function takes two arguments : link_location and friendly_name . Link_location is the destination or path the link should follow, entered as text. Friendly_name is the text that will be displayed with the link.

When a user clicks a cell that contains the HYPERLINK function, Excel will open the file or page specified by link_location. Link_location can be a cell reference or named range, a path to a file stored on a local drive, a path a file on a server using Universal Naming Convention (UNC), or an internet path in Uniform Resource Locator (URL) format.

Example #1 - link to cell

To link to another cell in the same worksheet, prefix the cell with “#”:

=HYPERLINK("#Z100","link to Z100") // cell in same sheet

Example #2 - link to sheet

To link to another sheet in the same workbook, use “#” with the Sheet name like this

=HYPERLINK("#Sheet2!A1","Sheet2") // sheet2 in same workbook

If the sheet name contains a space, you’ll get an invalid reference error with the formula above. In that case, you’ll need to enclose the sheet name in single quotes (’) like this:

=HYPERLINK("#'Sheet 2'!A1","Sheet 2") // sheet name with space

Example #3 - external link

To link to https://exceljet.net/ with the text “exceljet”:

=HYPERLINK("https://exceljet.net/","exceljet")

Example #4 - email link

To link to a valid email address in A1, you can concatenate “mailto:” like this:

=HYPERLINK("mailto:"&A1,"email") // link to email address in A1

With two email addresses in A1 and A2, you can create a link like this:

=HYPERLINK("mailto:"&A1&","&B1,"email") // two emails

This formula example explains how to construct a more complete mailto email link with cc, subject, body, etc.

Notes

  • Link_location should be supplied as a text string in quotation marks or a cell reference that contains the link path as text.
  • If friendly_name is not supplied, the HYPERLINK will display link_location as the friendly_name .
  • To select a cell that contains HYPERLINK without following the link, use arrow keys or right-click the cell.