Excel Training, Excel VBA Training, Excel Software Design

Excel HLOOKUP Function - Microsoft Excel HLOOKUP Function Tutorial

Excel HLOOKUP Function - Microsoft Excel HLOOKUP Function Tutorial

Microsoft Excel HLOOKUP Function Tutorial

The HLOOKUP Function looks up a value you designate in the first row of a worksheet area and returns a value from the column it finds the lookup value in. Used to merge tables, construct dashboards and perform various data processing tasks.

What is the Microsoft Excel HLOOKUP Function?

The HLOOKUP function is a computer program that you run from a worksheet cell formula. It looks up a value you designate in the first row of a worksheet area (called a range) and returns a value from the column it finds the lookup value in. You run the HLOOKUP worksheet function by typing its name in a formula then followed by the information it is suppose to use. The HLOOKUP worksheet function is used in applications when you have to look up prices, name information, scientific data anything that is contained in a table. It can also be used to merge two tables together transferring information from one to the other. The H in HLOOKUP stands for horizontal. There is also another function called VLOOKUP that looks along the first column of a table. Use VLOOKUP when your comparison values are located in the first column of the data that you want to find. Note that this function also has the capability to search for parts of text within other text when evaluating its lookup value.


How Do You Type the HLOOKUP Worksheet Function in a Formula?

Whenever you type a formula in a worksheet cell, this is called syntax or grammar. The general HLOOKUP function syntax has a format like this when you type it in a worksheet cell:

= HLOOKUP( lookup_value, table_array, row_index_num, [range_lookup] )

Where lookup_value, table_array, row_index_num, [range_lookup] are called the function argument list. Remember, you are running a computer program at this point so the program needs information to operate and that is why there is an argument list. When you see an argument list and you see square brackets [ ] around the argument name, this means the argument is optional and you do not have to include it when typing unless you need it. So for the syntax above, you need to include 3 arguments for the HLOOKUP worksheet function when typing it in a worksheet cell formula in order for it to calculate correctly. What argument values can be used are discussed below. Remember functions expect certain things in their argument lists, if you do not put the correct information in the list they will generate an error when run.


VLOOKUP Worksheet Function Argument Definitions

  • A constant, cell reference, formula or function that generates the value to search for in the first row of your data. Be sure there are no repeated values in this row. If there are, HLOOKUP will lock onto the first one encountered and ignore the rest. The values searched for in the first row can be text, numbers or logical values. Remember a date value must be converted to a number (see DATEVALUE function) before using, you cannot just type 1/1/2012. If text is present, they are not case sensitive. If searching for a text value, you can use wild card characters like ? and * to go after a specific character or a group of characters. You can also use the ampersand (&) to help concatenate the lookup value on the fly. Be sure to surround text lookup values with quotes " ", numeric lookup values do not take quotes.
  • table_array: A range reference, formula or function that generates a range of two or more rows of data for example A1:B2. The INDIRECT function can be used to generate a range.
  • row_index_num:  A constant number, cell reference, formula or function that generates the row number in the column that contains the value you want to return. For example, say you are looking up a value in the first row of a table, then in that column where the value is found you want to return the third row down from the first row, then you would generate the value 3 for this argument. Note that if you do not indicate the proper row say, 4 instead of 3 is generated and there are only 3 rows of data, an error will be returned.
  • range_lookup (Optional):  HLOOKUP can find an absolute match or approximate match in the first row of data it is looking in. Use TRUE for this argument if you want an approximate match or FALSE if you want an absolute match.
    • If you use TRUE or do not include it, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first row of your data must be placed in ascending sort order; otherwise, HLOOKUP may not give the correct value. You can put the values in ascending order by sorting the data.
    • If you use FALSE, HLOOKUP will only find an exact match. In this case, the values in the row column of data do not need to be sorted. If there are two or more values in the first column that match the lookup_value argument, the first value found is used. If an exact match is not found, the error value #N/A is returned.

How Do You Run the HLOOKUP Function?

Since the HLOOKUP function is a computer program, it runs when you press Enter to enter the formula that contains it. If any of the arguments are wrong, the function will return an error.


What Do I Type for a HLOOKUP Function Argument?

When typing the HLOOKUP function in a worksheet cell formula, you need to replace the argument list with arguments separating each one with a comma (,). Some typical arguments you can use are:

Cell Formula Explanation
= HLOOKUP( "Widgets", A1:D5, 2, FALSE ) Find Widgets in the A1:D1 range then return information from row 2 in the column that Widgets was found in
= HLOOKUP( "*ST*", G1:J5, 4, FALSE ) Find the piece of text ST inside the cell value text in the G1:J1 range then return information from row 4 in the column that ST was found in
= HLOOKUP( "*" & B1 & "*", G1:J5, 2, FALSE ) Find the piece of text from the B1 cell value inside the text in the G1:J1 range then return information from row 2 in the column that the text was found in
= HLOOKUP( 16, B1:D5, 5, FALSE ) Find number 16 in the B1:D1 range then return information from row 5 in the column that 16 was found in
= HLOOKUP( B1, Prices, 3, FALSE ) Find the B1 cell value in range name Prices first row then return information from row 3 in the column that the B1 cell value was found in *

* This function is used a great deal in building adaptive formulas that figure out ranges, start and stop of data.... It is also heavily used in dashboard design tying numeric ActiveX control outputs into formulas that seek out data in tables.


Additional VLOOKUP Function Examples

The worksheet seen below contains some typical worksheet formulas that run the VLOOKUP worksheet function. Pay close attention to the argument list and the syntax used to write the formula.

A B C
1 Searching by Names
2
3 Item Cost
4 Widget1 $1.25
5 Widget2 $2.50
6 Widget3 $3.50
7
8 Look up Cost header and return info about Widget1 cost. Formula below
9 Widget1 $1.25 = HLOOKUP( "Cost", A3:B6, 2, FALSE )
10
11 Item Cost Allow user to search for header Cost and return info about Widget1 cost. Formula below
12 Widget1 $1.25 = HLOOKUP( B11, A3:B6, 2, FALSE )
13
14 Item Cost Allow user to search for Cost header, use range name Widgets in place of A3:B6 much easier, can be on different sheet. Formula below
15 Widget1 $1.25 = HLOOKUP( B14, Widgets, 2, FALSE )
16


Things to Know About the HLOOKUP Function

  • When searching text values in the first row of your data, ensure that the data in the first row does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, HLOOKUP may give an incorrect or unexpected value. For more information on functions that you can use to clean text data, see the Text functions.
  • When searching for number or date values, ensure that the data in the first row is not stored as text values. In this case, HLOOKUP may give an incorrect or unexpected value. For more information, see Convert numbers stored as text to numbers in help.
  • If the range_lookup argument is FALSE and the lookup_value argument is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.
Our Available Public Excel Seminars/Webinars
Microsoft Excel Seminars/Webinars
Beginning Excel Training for Business Professionals Intermediate Excel Training for Business Professionals Advanced Excel Training for Business Professionals Beginning Excel VBA Training for Business Professionals Intermediate Excel VBA Training for Business Professionals Advanced Excel VBA Training for Business Professionals Advanced Excel Training for Engineers / Scientists Beginning Excel VBA Training for Engineers / Scientists Intermediate Excel VBA Training for Engineers / Scientists Advanced Excel VBA Training for Engineers / Scientists Microsoft Excel Dashboards Training
Beginning Excel Training for Business Intermediate Excel Training for Business Advanced Excel Training for Business Beginning Microsoft Excel VBA Training for Business Intermediate Microsoft Excel VBA Training for Business Advanced Microsoft Excel VBA Training for Business Advanced Excel Training for Engineers / Scientists Beginning Excel VBA Training for Engineers / Scientists Intermediate Excel VBA Training for Engineers / Scientists Advanced Excel VBA Training for Engineers / Scientists Microsoft Excel Dashboard Training
We can also train your company onsite. For a list of all of our onsite Excel classes click here or contact us at 805.498.7162.


Site Map

Copyright © 2002-2017

EMAGENIT All Rights Reserved