LOOKUP vs. VLOOKUP and HLOOKUP

How it differs is that:

With VLOOKUP and HLOOKUP, you can choose which column or row to return a data value from. LOOKUP always returns a value from the last row or column in the array. When finding a match for the specified value (the Lookup_value), VLOOKUP only searches the first column of data and HLOOKUP only the first row. The LOOKUP function searches either the first row or column, depending on the shape of the array.

LOOKUP Function and Array Shape

The shape of the array can be either a square (equal number of columns and rows) or a rectangle (unequal number of columns and rows). The shape affects where the LOOKUP function searches for data:

If an array is square or if it is a tall rectangle (taller than it is wide), LOOKUP assumes that the data is arranged in columns and 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 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_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): Range cells that the function searches to find the Lookup_value. The data can be text, numbers, or logical values.

Example Using the Array Form of the LOOKUP Function

This example uses the Array Form of the LOOKUP function to find the price of a Whachamacallit in the inventory list. The shape of the array is a tall rectangle, and the function returns a value located in the last column of the inventory list. To follow along with this example, enter the data shown in the sample worksheet below.

Sort the Data

You must sort the data in the array in ascending order so that the LOOKUP function works properly. When sorting data in Excel, select the columns and rows of data to sort first, which typically includes the column headings.

LOOKUP Function Example

Although it is possible to type the LOOKUP function, =LOOKUP(A2,A5:C10), into a worksheet cell, you might find it less confusing to use the function’s dialog box. The dialog box lets you enter each argument on a separate line without worrying about the function’s syntax, such as parenthesis and the comma separators between arguments. The steps below detail how the LOOKUP function was entered into cell B2 using the dialog box.

Enter a Lookup Value

Here’s how to enter a name to find the price of an item: