New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

LOOKUP made easy

Free Excel Tips and Tutorials

LOOKUP made easy

Postby Sisyphus » Tue Feb 07, 2012 6:08 am

Before you get to read Excel's help about the array form of the LOOKUP function they tell you, by way of introduction, that you better use HLOOKUP or VLOOKUP which are more capable. But when you dutifully follow their advice you soon discover that greater capability comes with greater complexity whereas for your simple needs LOOKUP may, in fact, be the very best solution. LOOKUP is the easiest way to return a value from a list, in Excel jargon 'array', of values. This is the syntax:
Code: Select all
= LOOKUP ( lookup_value, lookup_array )

That looks very easy and it is. But hardly have you decided that you want to be interested the great teachers at Microsoft hit you with one of those sentences you need to read twice before understanding half its meaning:
If LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
I shall explain ALL the meaning in one word: Nonsense!
The simple truth they hide between the words is that LOOKUP always returns 'the largest value in the array that is less than or equal to lookup_value' - with no further conditions to it. This principle is repeated in many of Excel's more advanced look-up functions and can easily be learned here.

But, asks the inquisitive mind, does it make sense to look for a value I already have? Indeed it doesn't and that is why you would use the function for another purpose, such as determining which group of values your lookup_value belongs to. Sorting values into bins, as it were, is a very common requirement in many calculations. It is true that the array form of LOOKUP usually only provides one of several elements of a formulas calculating such results and that HLOOKUP or VLOOKUP often offer inclusive solutions. But it is at least equally true that LOOKUP's vector form offers another, different capability at least on par with the other two - a fact often left undiscovered by earnest students following the Excel helpers' advice to move on. However, the narrow constraints of this short post leave no room to deal with any of the above. Instead, I want to point at generalities that are often not conveyed while trying to teach the 'big picture'. This is a simple example of LOOKUP:
Code: Select all
= LOOKUP(A1, B1:B10)

In this formula both, the lookup_value and the lookup_array consist of variables. I come accross many instances where this isn't required. Therefore it is worth mentioning that =LOOKUP(9.97, B1:B10) works as well as =LOOKUP("Apples", B1:B10), just remember that B1:B10 must be in ascending order. The more frequent requirement, however, is where the lookup_value is variable and the lookup_array isn't, and it is quite hard to find instructions on how to write an actual array into a formula when you want it. You use curly braces for that, like this:
Code: Select all
=LOOKUP(A1,{0,100,300,500,1000}) * 0.1

This formula can be used to allocate bonus credits for good performance at school. A student having earned between 100 and 200 credits will get a bonus of 10 (= 100 * 0.1), but if he can reach or exceed 300 he or she will receive a bonus of 30. And you don't even need a lookup_array, because it's all in the formula.
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 21 guests