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

More than one way to solve a problem



I am often amazed at how people use Excel to solve problems. Actually, what amazes me is how people can create complicated solutions to solve simple problems.

Case in point. A co worker of mine prepared a spreadsheet that counted the number of times an item appeared in a list. The formula they used worked but it struck me as overkill. Here is a sample of their solution. 

They had an item list in column A that had item numbers repeated throughout the list. They also had a master list of items in column c. This master list had only 1 of every item number in column A. In column D they calculated how many times the item in column C appeared in column A. They combined the SUMPRODUCT and EXACT functions to get their answer and it works.


A
B
C
D
E
1
Item List

Item #
Count
Formula
2
1

1
3
=SUMPRODUCT(--EXACT(A:A,C2))
3
2

2
4
=SUMPRODUCT(--EXACT(A:A,C3))
4
3

3
2
=SUMPRODUCT(--EXACT(A:A,C4))
5
2

4
1
=SUMPRODUCT(--EXACT(A:A,C5))
6
5

5
2
=SUMPRODUCT(--EXACT(A:A,C6))
7
4




8
1




9
2




10
3




11
1




12
5




13
2





When I inquired as to why they didn’t just use the countif function, they replied, “what’s that?”

It dawned on me that they didn’t know the countif function and developed a work around using what they did know. The more I thought about it, the more I came to realize that isn’t that what each of does with Excel? We use what we know. One of Excel’s strengths lies in its flexibility for solving problems in a multitude of ways.

So what was my recommended way of solving the above problem? I used the count if function in column H to return the same results.

A
B
C
D
E
F
G
H
I
Item List

Item #
Count
Formula

Item #
Count
Formula
1

1
3
=SUMPRODUCT(--EXACT(A:A,C2))

1
3
=COUNTIF(A:A,C2)
2

2
4
=SUMPRODUCT(--EXACT(A:A,C3))

2
4
=COUNTIF(A:A,C3)
3

3
2
=SUMPRODUCT(--EXACT(A:A,C4))

3
2
=COUNTIF(A:A,C4)
2

4
1
=SUMPRODUCT(--EXACT(A:A,C5))

4
1
=COUNTIF(A:A,C5)
5

5
2
=SUMPRODUCT(--EXACT(A:A,C6))

5
2
=COUNTIF(A:A,C6)
4








1








2








3








1








5








2









So how would you have solved the above problem? Do you have a different solution then the ones presented above? Break out your mad crazy Excel sills and let us know.

Beginner, CountIf, SumProduct, Exact