What is the syntax of lookup formula?

LOOKUP Formula is basic LookUp function in Excel, it is provided for backward compatibility. LookUp function is helpful if you want to look up a value in either in one-row or one-column data range or any array.

When we have array of values, and if you want to extract the result of a particular value from the data. We can use LookUp Formula to do this. Below is the pictorial representation of LOOKUP Formula.

Here we have List of Weekdays (Sunday to Saturday) and its IDs (1,2,…7). And we can specify the Lookup Value and Lookup Array in the Formula as show n below to lookup the respective value.

Here, A1:B7 is lookup-range and D4 is the lookup_value. And we will get the Wednesday as result for the lookup value(4).

What is the use of LOOKUP function?

LOOKUP Function in Excel returns a value from a table or from an array.

What is the syntax of LOOKUP function?

• Syntax #1: LOOKUP(lookup_value, array): It is helpful when we have the single set of range of array with lookup vector.
• Syntax #2: LOOKUP(lookup_value, lookup_vector, result_vector): It is helpful when we have two different arrays one for lookup vectors and the other one for lookup arrays or range

• lookup_value: The value you want to look for
• array: The array of values that contain the value you want to look for
• lookup_vector: The range of cells that contains the value you want to look for
• result_vector: The range of cells that contains the value you want to return

Examples on LOOKUP Function in Excel

Syntax #1

LOOKUP function searches for the value in the first row or column of an array and returns the corresponding value in the last row or column of the array.

Example 1:

=LOOKUP(3,{1,2,3,4;”One”,”Two”,”Three”,”Four”})

Looks for 3 in the array and returns its corresponding value i.e; “Three”.

Example 2:
=LOOKUP(3,A5:B9)

Looks for lookup value (4) in the array (first column of the lookup range. i.e; A5:A9) and returns its corresponding value (right or second column of the lookup range. i.e; B5:B9).

Syntax #2

LOOKUP function searches for value in the lookup_range and returns the corresponding value in the result_range.

Example 3: =LOOKUP(4,A15:A19,B15:B19)

Looks for 4 in the lookup range (first set of range in the formula i.e; A15:A19) and returns its corresponding value from the result range (second set of the range in the formula i.e; B15:B19)

Learning Outcomes

• Use lookup functions and formulas

Lookup functions in Excel are used for looking through a single column or row to find a particular value from the same place in a second column or row. This often takes place when there are multiple worksheets within a workbook or a large amount of data in a worksheet. Lookup functions are very helpful with large sets of data to find specific information across many rows or columns.

VLOOKUP (Vertical Lookup)

Looks for a value in the leftmost column of a table and returns a value in the same row from the vertical column you define. The lookup value must always be in the leftmost column of the table.

The Excel formula is written =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

This table includes the sales totals for salespeople. In this case you want to find out what the sales were for certain salespeople. This is a small example but imagine a long list of names and numbers to search through. Using the VLOOKUP function quickly brings you the results you want. Be sure to use F4 to create the absolute references and keep the table array the same for each lookup. Also make sure and make the col_index_num use the second column in the table (column 2 in the table) with a return for the range_lookup as FALSE. By using FALSE in all cases, there is no need to sort your tables into ascending order. Here is what those results look like.

For another VLOOKUP example, watch this video of how to use this function to find parts that are in stock and the supplier for particular parts.

HLOOKUP (Horizontal Lookup)

Looks for a value in the topmost row of a table and returns a value in the same row from the horizontal column you define. The lookup value must always be in the leftmost column of the table.

The Excel formula is written =HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

MATCH and INDEX Functions

Now we’ll learn two additional lookup functions, Match and Index. Watch this video to learn how to use Match and Index lookup functions. Here are the descriptions and formulas for creating them in Excel.

• MATCH – Function that returns a position of a value in a range which can be a row, column or table. The Excel formula is written =MATCH(lookup_value,lookup_array,[match_type])
• INDEX – Function that returns a position of a value in a range which looks at the intersection of a column and row position in a table. The Excel formula is written =INDEX(reference,row_num,[column_num],[area_num])

As you become more familiar with lookup functions, try out a few more by selecting the Formulas tab, Lookup & Reference button and see what others are available for you to try.

Contribute!

Did you have an idea for improving this content? We’d love your input.

Improve this pageLearn More