LOOKUP Function Array Form Overview
How it differs is that:
- With VLOOKUP and HLOOKUP you can choose which column or row to return a data value from, while LOOKUP always returns a value from the last row or column in the array.
- In trying to find a match for the specified value - known as the Lookup_value - VLOOKUP only searches the first column of data and HLOOKUP only the first row, while the LOOKUP function will search either the first row or column depending on the shape of the array.
LOOKUP Function and Array Shape
The shape of the array - whether it is square (equal number of columns and rows) or a rectangle (unequal number of columns and rows) - affects where the LOOKUP function searches for data:
- If an array is square in shape or if it is a tall rectangle (taller than it is wide), LOOKUP assumes that the data is arranged in columns and therefore searches for a match to the Lookup_value in the first column of the array.
- If an array is a wide rectangle (wider than it is tall), LOOKUP assumes that the data is arranged in rows and therefore searches for a match to the Lookup_value in the first row of the array.
The LOOKUP Function Syntax and Arguments - Array Form
The syntax for the Array Form of the LOOKUP function is:
= LOOKUP(Lookup_value, Array)
Lookup_value (required) - a value that the function searches for in the array. The Lookup_value can be a number, text, a logical value, or a name or cell reference that refers to a value.
Array (required) - a range cells that the function searches to find the Lookup_value. The data can be text, numbers, or logical values.
- For the LOOKUP function to work correctly, the Array argument must be sorted in ascending order (A to Z or smallest to largest for numbers)
- If the function cannot find an exact match for the Lookup_value, it chooses the largest value in the Array that is less than or equal in value to the Lookup_value
- If the Lookup_value is missing or smaller than all values in the Array, the LOOKUP function will return an #N/A error
Example Using the Array Form of the LOOKUP Function
As seen in the image above, this example will use the Array Form of the LOOKUP function to find the price of a Washer in the inventory list.
Entering the data below in the assigned cells will result in a tall rectangle shape for the array. Consequently, the function will return a value located in the last column of the inventory list.
Entering the Tutorial Data
- Enter the following data into cells D1 to F5
Cell Data D1 - Part Name E1 - Price D4 - Part D5 - Widget D6 - Bolt D7 - Bearing D8 - Gear D9 - Cog D10 - Washer E4 - Size E5 - L E6 - M E7 - S E8 - S E9 - L E10 - M F4 - Price F5 - $14.76 F6 - $1.54 F7 - $17.34 F8 - $23.56 F9 - $20.21 F10 - $1.43
Sorting the Data
As indicated in the notes above, the data in the array must be sorted in ascending order so that the LOOKUP function will work properly.
When sorting data in Excel it is necessary to first select the columns and rows of data to be sorted. Normally this includes the column headings.
- Highlight cells E4 to D10 in the worksheet
- Click on the Data tab of the ribbon menu
- Click on the Sort option in the middle of the ribbon to open the Sort dialog box
- Under the Column heading in the dialog box choose to sort by Part from the drop down list options
- If necessary, under the Sort on heading choose Values from the drop down list options
- If necessary, under the Order heading choose A to Z from the drop down list options
- Click OK to sort the data and close the dialog box
- The order of data should now match that seen in the image above
Opening the LOOKUP Function Dialog Box
Although it is possible to just type the LOOKUP function into a cell in a worksheet, many people find it easier to use the function's dialog box to enter the function.
The dialog box lets you enter each argument on a separate line without worrying about the function's syntax.
- 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, array option in the list
- Click OK to bring up the Function Arguments dialog box
Entering the Function's Arguments
- 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 Array line in the dialog box
- Highlight cells D5 to F10 in the worksheet to enter this range in the dialog box - this range contains all of the data to be searched by the function
- 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 the cell D2
Entering a Lookup Value
- Click on cell D2, type Washer and press the Enter key on the keyboard
- The value $1.43 should appear in cell E2 as this is the price of a washer located in the last 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:F10) appears in the formula bar above the worksheet