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
- Enter the following data into cells D1 to F5.
- Click on cell E2 in the worksheet - this is where the results of the function will be displayed
- Click on the Formulas tab of the ribbon menu
- Choose Lookup and Reference from the ribbon to open the function drop down list
- Click on the LOOKUP in the list to bring up the Select arguments dialog box
- Click on the lookup_value, lookup_vector, result_vector option in the list
- Click OK to bring up the Function Arguments dialog box
- In the dialog box, click on the Lookup_value line
- 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
- Click on the Lookup_vector line in the dialog box
- Highlight cells D5 to D10 in the worksheet to enter this range in the dialog box - this range contains the part names
- Click on the Result_vector line in the dialog box
- 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
- Click OK to complete the function and close the dialog box
- An #N/A error appears in cell E2 because we have yet to type a part name in cell D2
- Click on cell D2, type Gear and press the Enter key on the keyboard
- 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
- Test the function by typing other part names into cell D2. The price for each part in the list will appear in cell E2
- When you click on cell E2 the complete function =LOOKUP (D2, D5:D10, E5:E10) appears in the formula bar above the worksheet
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
