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
