Skip to main content

How to Use the LOOKUP Function in Excel

Joshua earned an MBA from USF and he writes mostly about software and technology.

The LOOKUP function allows one to search for data in a column or row and return a piece of data in another row or column. The LOOKUP function is shown here searching for the value 5.75 and will return the corresponding value from the second array.

The LOOKUP function allows one to search for data in a column or row and return a piece of data in another row or column. The LOOKUP function is shown here searching for the value 5.75 and will return the corresponding value from the second array.

The Purpose of the LOOKUP Function

The LOOKUP function checks the column farthest to the left in a spreadsheet range for a value and returns a value that correlates with that value from another range. The syntax for this function can be seen in detail with argument explanations below.

The LOOKUP Function Syntax

The LOOKUP function must be inputted like a formula to work. To manually add this formula to a cell the following steps need to be taken:

  1. A cell needs to be clicked and "=LOOKUP(" needs to be typed.
  2. After the open parenthesis, the lookup up value must be added.
  3. Next, the lookup vector must be added followed by a comma.
  4. The optional vector result is added if applicable.
  5. Finish with a closing parenthesis and then the formula can be entered into the cell.

Below the syntax of the LOOKUP function can be found in bold along with detailed explanations of each argument.

LOOKUP(lookup_value, lookup_vector, [result_vector])

  • Lookup Value - This is a requirement. This is the value that the function searches for in the first vector (first column) unless the vector is specified. This value can be text, a number, a reference, or even a logical value.
  • Lookup Vector - This is a requirement. This is a range that contains only one column if using the result vector or multiple columns is leaving the result vector out. These values can be numbers, text, or logical values. When used alone the first column of this range will be where the lookup value is trying to match and the last column will contain the corresponding result.
  • Result Vector - This optional input is a range that contains only one column. A range that contains only one row or column. It is necessary for the result vector array to be the same size as the lookup vector. Note, if the result vector is not used that the lookup value or an error will display if the lookup value cannot be found.

LOOKUP Function Example

Suppose employee records in a worksheet need to be returned based on the employee's identification number. This type of process can be made possible with the LOOKUP function.

The spreadsheet below has employee data at a company and I need to display criteria about employees based on their employee ID.

how-to-use-the-lookup-function-in-excel

In the example below, the first option is used to return an item from the second array of departments. The employee ID number that appears in cell F13 will determine what department appears in cell G3 where the formula appears.

how-to-use-the-lookup-function-in-excel

In the second example below, only one array is used. In this situation, the value in the first column of the array will be looked up and the corresponding phone number data in the array will be returned because it is the last column of the array.

how-to-use-the-lookup-function-in-excel
Scroll to Continue

Inserting the LOOKUP Function

To insert this function from the function library a cell is first be selected. Next, the formulas tab is selected and the "Lookup & Reference" button in the function library needs to be selected. A drop-down will appear and "LOOKUP" from the list should be chosen.

CHANGE THIS PHOTO

The illustration shows how to insert the LOOKUP function by selecting Formulas→Lookup &  Reference→LOOKUP.

The illustration shows how to insert the LOOKUP function by selecting Formulas→Lookup & Reference→LOOKUP.

Next, you will need to select one of two argument configurations that you would like to use.

After inserting a function, you must choose how you plan to use the function.

After inserting a function, you must choose how you plan to use the function.

Below I choose the vector option and the fields are ready to input data. The insert option is great because when you clicked in the argument fields instruction are given to help you enter the function. Additionally, the result will be displayed before you enter the function by selecting OK.

The functional arguments window is where inputs to functions are added to complete the insertion. This is one of the two functional argument lists for this function.

The functional arguments window is where inputs to functions are added to complete the insertion. This is one of the two functional argument lists for this function.

References

Microsoft. (n.d.). LOOKUP function. Retrieved January 5, 2020, from https://support.office.com/en-us/article/lookup-function-446d94af-663b-451d-8251-369d5e3864cb.

How to Use the COUNT Function in Excel

How to Use the AVERAGE Function in Excel

How to Use the ABS Function in Excel

How to Remove Errors in Excel With the IFERROR Function

How to Use the VLOOKUP Function in Excel

Converting Measurement Units: Using the CONVERT Function

To learn more about using functions in Excel I recommend The Excel 2019 Bible book. I have been using the Excel Bible for years to improve my understanding of all aspects of this Microsoft product.

This content is accurate and true to the best of the author’s knowledge and is not meant to substitute for formal and individualized advice from a qualified professional.

© 2022 Joshua Crowder

Related Articles