• Share
Send to a Friend via Email

### Your suggestion is on its way!

An email with a link to:

was emailed to:

Thanks for sharing About.com with others!

# Excel LOOKUP Function Tutorial

Excel LOOKUP Function Tutorial - Vector Form

Excel LOOKUP Function Tutorial

### 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

Ted French