1. Computing

Excel LOOKUP Function Tutorial

By

Excel LOOKUP Function Tutorial - Vector Form
Excel LOOKUP Function Tutorial

Excel LOOKUP Function Tutorial

© Ted French

LOOKUP Function Overview

The LOOKUP function is similar to other Excel lookup functions such as VLOOKUP and HLOOKUP in that it can be used to return a value from a table of data in Excel.

How it differs is that while VLOOKUP only returns data from columns of data and HLOOKUP only from rows, the LOOKUP function can return data from a vector - a single row or column of data - or from an array - a block of data containing multiple rows or columns.

To accomplish this, the function has two forms: the Vector Form and the Array Form.

Vector and Array Forms

The Vector Form will search a single row or column for a specified value and then return a value from the same position in a second row or column.

The Array form looks in the first row or column of a block of data for the specified value, and then returns a value from the same position in the last row or column of the block.

This tutorial focuses on the Vector Form of the function.

The LOOKUP Function Syntax and Arguments - Vector Form

The syntax for the Vector Form of the LOOKUP function is:

= LOOKUP(lookup_value, lookup_vector, [result_vector])

Lookup_value (required) - a value that the function searches for in the first vector. The Lookup_value can be a number, text, a logical value, or a name or cell reference that refers to a value.

Lookup_vector (required) - a range containing only one row or column that the function searches to find the Lookup_value. The data can be text, numbers, or logical values.

Result_vector (optional) - a range that contains only one row or column. The result_vector argument must be the same size as lookup_vector.

Notes:

  • For the LOOKUP function to work correctly, the Lookup_vector must be sorted in ascending order (A to Z or smallest to largest for numbers)
  • If the Result_vector argument is omitted, the function returns the Lookup_value argument if it is present in the Lookup_vector
  • If the function cannot find an exact match for the Lookup_value, it chooses the largest value in the Lookup_vector that is less than or equal in value to the Lookup_value
  • If the Lookup_value is smaller than all values in the Lookup_vector, the LOOKUP function will return an #N/A error

Example Using the Vector Form of the LOOKUP Function

As seen in the image above, this example will use the Vector Form of the LOOKUP function to find the price of a Gear in the inventory list.

Entering the Tutorial Data

  1. Enter the following data into cells D1 to F5.
  2. Cell    Data
     D1  -  Part Name
     E1  -  Price
    
     D4  -  Part 
     D5  -  Bearing
     D6  -  Bolt
     D7  -  Cog
     D8  -  Gear
     D9  -  Washer
     D10 -  Widget 
    
     E4  -  Price 
     E5  -  $17.34
     E6  -  $1.54
     E7  -  $20.21
     E8  -  $23.56
     E9  -  $1.43
     E10 -  $14.76
    

    Entering the Function's Arguments

  3. Click on cell E2 in the worksheet - this is where the results of the function will be displayed

  4. Click on the Formulas tab of the ribbon menu

  5. Choose Lookup and Reference from the ribbon to open the function drop down list

  6. Click on the LOOKUP in the list to bring up the Select arguments dialog box

  7. Click on the lookup_value, lookup_vector, result_vector option in the list

  8. Click OK to bring up the Function Arguments dialog box

  9. In the dialog box, click on the Lookup_value line

  10. Click on cell D2 in the worksheet to enter that cell reference into the dialog box - in this cell we will type the part name that we are searching for

  11. Click on the Lookup_vector line in the dialog box

  12. Highlight cells D5 to D10 in the worksheet to enter this range in the dialog box - this range contains the part names

  13. Click on the Result_vector line in the dialog box

  14. Highlight cells E5 to E10 in the worksheet to enter this range in the dialog box - this range contains the prices for the list of parts

  15. Completing the Function

  16. Click OK to complete the function and close the dialog box

  17. An #N/A error appears in cell E2 because we have yet to type a part name in cell D2

  18. Click on cell D2, type Gear and press the Enter key on the keyboard

  19. The value $20.21 should appear in cell E2 as this is the price of a gear located in second column of the data table

  20. Test the function by typing other part names into cell D2. The price for each part in the list will appear in cell E2

  21. When you click on cell E2 the complete function =LOOKUP (D2, D5:D10, E5:E10) appears in the formula bar above the worksheet

©2014 About.com. All rights reserved.