Lookup (array)

Today I want to address a shortcut of sorts. A substitute for VLookup using Vlookups cousin, Lookup. Now just because Lookup is missing the V in the front, doesn't make is any less useful.

Let's see how you can use the Lookup function (array) in place of VLookup. In this example the Lookup formula in cell D3 returns the corresponding item number that is entered in cell E1. Now VLookup can do the same function but requires additional components to make the function work. So Lookup can be easier to use.

	A	B	C	D	E
1	Item	Color			15003
2	51671	Hammer
3	15003	Saw		Result	Function
4	31536	Ladder		Saw	=LOOKUP(E1,A1:B6)
5	53342	Level
6	21367	Pry Bar		Saw	=VLOOKUP(E1,A1:B6,2,FALSE)

Syntax

Lookup value, array

Lookup Value – A value that Lookup searches for in an Array. It can be a number, text, logical value or a name or reference. In the above example my lookup value is 15003.

Array – A range of cells that contain text, number, or logical values that you want to compare with Lookup_value. In the above example my array is Cells A1 – B6.

Basically all you need to know is what you want to find and where you want to find it in. Lookup will find the value and return the corresponding value in the last column in your array.

The array form of LOOKUP is similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array. 

If an array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for lookup_value in the first row. 

If array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column. 

With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column.

BEGINNER, LOOKUP, VLOOKUP, HLOOKUP