Excel lookup function

Excel has three functions to look up information within the worksheet or other worksheets, LOOKUP, VLOOKUP and HLOOKUP.  All three can be found in the FORMULA Tab in the Ribbon under Lookup & Reference as highlighted.

Excel lookup function

The LOOKUP Function should be used when you are searching a single row or column for one piece of data in order to retrieve another piece of information within the same position. For example, you know the stock code but want to know what the price of the stock is. You’d use LOOKUP to locate the stock code and retrieve the price.

The VLOOKUP or Vertical Lookup should be used when you need to find data within the worksheet by row. Similar to LOOKUP you would use this to search by stock code to find the price by searching across the row. HLOOKUP or Horizontal Lookup is used when you want to search down columns for information, it starts by searching the first row for the value and then moves down the column to find the second value. Let’s look at the three lookup functions to see which is best during certain circumstances.read more information about logical operators by clicking here

LOOKUP Function

When LOOKUP is selected from the Lookup & Reference Button a dialog box will appear asking which argument you will be using Vector or Array.

Excel lookup function

Vector is recommended when using the LOOKUP Function. If you would like to use Array it is strongly recommended you use the VLOOKUP or HLOOKUP Functions instead. The Vector form searches a specific vector (either one row or one column range) for a value and then returns a value from that same position in a different vector.  The formula for the function is

=LOOKUP(lookup_value,lookup_vector,return_vector)

Excel lookup function

Searching down Columns

Example 1: Find the Code for “Property”

Lookup Value = Property

Lookup Vector = Column A

Return Vector = Column B

=LOOKUP(“Property”,A2:A20,B2:B20)

Example 2: What type of Account is Code 600

Lookup Value = 600

Lookup Vector = Column B

Return Vector = Column C

=LOOKUP(600,B2:B20,C2:C20)

Excel lookup function

Example 1: Who didn’t have any sales during Quarter 1?

Lookup Value = 0

Lookup Vector = Row 4

Return Vector = Row 1

=LOOKUP(0,A4:F4,A1:F1)

Excel lookup function

VLOOKUP and HLOOKUP

Vertical Lookup searches an array by starting down the first Column to find a value and then moving across the row that the value is in to locate the result.

Horizontal Lookup searches an array by starting across the first Row to find a value and then moving down the column that the value is in to locate the result.

A visual for the path the two functions is to the right. Based on this visual you can determine which function will work best with your worksheet or table.

Use VLOOKUP when the your comparison values are located within a column to the left of the data you are trying to return and use HLOOKUP when your comparison values are located within a column above the data you are trying to return.

The formula or syntax for both functions have the same or similar elements:

Lookup_value: In VLOOKUP, this is the value to search for in the first column. In HLOOKUP, it is the value to search for in the first row. This is often called the comparison value because this is the value Excel will compare to the worksheet to know when to search across (VLOOKUP) or down (HLOOKUP).

Table_Array: In both functions, this is the area or table where the data that you are searching is contained.

Col_ind_num: This is only for VLOOKUP and is the number of the column that the desired result is in. Ex: Column B would be 2 and Column C is 3. This tells Excel when to stop moving across the row.

Row_ind_num: This is only for HLOOKUP and is the number of the row that the desired result is in. This tells Excel when to stop moving down the column.

Range_lookup: In both functions, this tells Excel whether you want an approximate match, TRUE, or an exact match, FALSE. This element is optional within both functions.

Excel lookup function

VLOOKUP EXAMPLES

Example 1: Create a quick query to search for Account Name based on Code Number.

  1. Add a box to enter the code into D6
  2. In D9 enter the VLOOKUP Function
    1. Lookup_Value = D6
    2. Table_Array = A1:C22
    3. Col_in_num = 2 (we want the second column value returned)
    4. Exact Match = FALSE
  3. =VLOOKUP(D6,A1:C22,2,FALSE)

Anytime a code number is entered into D6 you can quickly retrieve the Account Name.

The VLOOKUP Formula searches the first column to find the lookup_value but what if your lookup_value is in the second column.

Example 2: We want to look up Work In Progress (WIP) and find the type of account that it falls into (Group). To complete this task we set the Table_Array starting with Column B instead of Column A so that Column B is the first column. In cell D12 we enter the formula =VLOOKUP(“WIP”,B2:C22,2,FALSE) to get the result of “Current Asset”. The table array entered is B2:C22 so Excel skips over Column A. For quick reference change the lookup_value in the formula to be D9 (the result from the example above) and now when a code is entered into D6 not only will the account name appear in D12 but the group will appear in D12.

Excel lookup function

The formula now reads =VLOOKUP(D9,B2:C22,2,FALSE)

HLOOKUP EXAMPLES:

Excel lookup function

Example 1: Find the Quantity needed to fulfill Order # 10060

  1. In Cell B7 Enter the parameters of the function.
    1. Lookup_value = 10060
    2. Table_array = B1:K5
    3. Row_in_num = 3
    4. Exact Match = FALSE
  2. =HLOOKUP(10060,B1:K5,3,FALSE)
  3. The Result is 10.

Example 2: Order 10050 is complete, find the name of the buyer.

Excel lookup function

  1. In Cell B7 Enter the parameters of the function.
    1. Lookup_value = 10050
    2. Table_array = B1:K5
    3. Row_in_num = 2
    4. Exact Match = FALSE
  2. =HLOOKUP(10050,B1:K5,2,FALSE)
  3. The Result is Wells

Excel lookup function

  • |
  • Leave a Reply

    Your email address will not be published. Required fields are marked *