New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

LOOKUP function - Vector form

Free Excel Tips and Tutorials

LOOKUP function - Vector form

Postby Sisyphus » Wed Feb 08, 2012 4:11 am

The LOOKUP function can be used to read a telephone book: All the names are in column A, all the numbers in column B. You find the name in column A and retrieve the number from column B. In Excel Help jargon the name is the 'lookup-value', column A the 'lookup_array' and column B the 'result_vector'. The syntax, thus explained, is simple enough:
= LOOKUP( lookup_value, lookup_array, result_vector)
or
Code: Select all
=LOOKUP(A1, A10:A20, B10:B20)

The reason why Excel's Help talks of 'result_vector' instead of 'result_array' hasn't been disclosed. For all practical purposes the words vector and array are interchangeable in this context. Perhaps they wish to draw attention to the fact that the result vector need not be parallel to the lookup_array. The following two examples are both perfectly capable of functioning as well as the above:
Code: Select all
=LOOKUP(A1, A10:A20, A20:A30)
=LOOKUP(A1, A10:A20, B2:L2)

They also demonstrate the difference between LOOKUP and the related HLOOKUP and VLOOKUP functions. LOOKUP can fetch the result from any vector anywhere while the other two functions' result arrays must be parallel to the lookup array, either below or to the right. The relationship between lookup and result arrays of the LOOKUP function is in the position of their elements within each array. If A1 is found in the third element of the lookup_array, the third element of the result_vector will be returned regardless of whether the latter is positioned horizontally or vertically.

LOOKUP truly comes into its own when combining a variable lookup_values with fixed lookup and result arrays. Take this thought for example:
If your purchase is for $100 or more we grant you 2% discount.
If your purchase exceeds $250 we grant you 5% discount.
If your purchase exceeds $500 we grant 7.5% discount.
If your purchase is for at least $1000 you can have 10% discount.

Where the purchase value is in A1 this task can be solved with nested IF functions like this:
Code: Select all
NettPayable=A1 *(100-IF(A1<100,0,IF(A1<250,2,IF(A1<500,5,IF(A1<1000,7.5,10)))))/100

Using the LOOKUP function the same result can be achieved with this formula:
Code: Select all
NettPayable=A1*(100-LOOKUP(A1,{0,100,250,500,1000},{0,2,5,7.5,10}))/100

The advantage is not only in the shorter formula but also in its greater clarity of expression. Faults are much easier to find. Of course, the result would be still more professional if the arrays, here placed in curly braces, would be placed in a table instead. With the discount schedule placed in a range C1:G2 the LOOKUP function could look like this:
Code: Select all
NettPayable=A1*(100-LOOKUP(A1,$C$1:$G$1, $C$2:$G$2))/100
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Tips and Tutorials

Who is online

Users browsing this forum: No registered users and 27 guests