Monday, June 1, 2020

Home Excel tips How Excel LOOKUPs Work: Demystifying mighty functions with an example from past [1 of many]

How Excel LOOKUPs Work: Demystifying mighty functions with an example from past [1 of many]

June 01, 2020 Excel tips,

How Excel LOOKUPs Work: Demystifying mighty functions with an example from past [1 of many]

Functions to Excel is like Sense to Humans. Humans with absent sense are no better than crawlers walking on two legs, without functions Excel is just another program with ability to store information and doing simple maths.

In a nutshell functions made a spreadsheet program that can really excel at data. Functions are like ready made tools. And if we know how to use them then possibilities are as high as your creative mind can go, in other words sky’s the limit.

Talking about functions, although everyone of them is a gem at its place but some are born heroes and among them are Excel’s lookup functions. Although Excel comes with many lookup functions but the most known in this regard is VLOOKUP and after that HLOOKUP and the one of the greatest hits since last few years INDEX & MATCH.

Before we do the open heart surgery of each of these functions, lets understand the basic idea behind lookup functions and once we grasp the basics we will march on to understand each one of them. This is the first episode of many coming in the future about Excel’s LOOKUP functions

Lookup by itself is not the usual English language word so its origin is not far embedded in the history. But still its unclear from where this word has originated and how it came into being. But to the extent I researched it has probably resulted from Lookup Tables and surprisingly the same concept is the very foundation of lookup functions as well.

In other mathematics class if we you remember we usedlogarithm tables or annuity tables which we used to find the values. It was very easy to solve the question once we had the value with us. But finding the value was itself little systematic. This systematic approach was infact the lookup activity we used to perform and the tables were actually lookup tables. Lookup means, finding something following a certain path or manner.

Have a look at this annuity table.

This is no different from the one we used in our school days to find present value. And if you are an accountant then it must be a very old affair for you as accountants in their accounting classes and especially exams (even today) use these tables to find value and solve questions.

Following is part of the whole table.

Suppose we want to find out the present value factor for 7 years with interest rate (cost of capital) of 4%.

Remember the steps we used to do?

You can either go with the rate first or years first. But my teacher advised me to always go for the interest rate column first. Once you find the interest rate put the scale on the page horizontally along the yearly rows and slide down to the number of years for which you want the discount factor. The point where row of specific year and column of specific interest intersects have the value you are after.

The approach I learnt was going from rates to years. But you can go from year to interest as well. But how? Lets redo the steps this time in detailed manner:

Step 1: Identify the column containing number of years. That is the first column of the table.

Step 2: Find the number (of year) that we want. Once found halt at that row.

Step 3: Now jump to the column at right to reach the one that is of correct interest rate. In our example we have to jump 9 columns i.e. 10th column in the range (column of interest rate with 4%) from the first column (column of years).

Step 4: Fetch the value that is in the same row where the number of year is. In simple words where the column of 4% and the row of 7 years intersect bears the value. In our example the value is 6.00205

Now lets talk about technicalities of this simple approach used on the table given:

Data is divided into rows and columns
Along the column, data is arranged according to the interest rate.
Along the rows, data is arranged according to years.
Data is given in ascending order.
Because data is classified in rows and columns and also arranged in specific order, finding values is very easy and efficient.

This is how majority of you used to do LOOKUP when you were not even familiar with Excel at all.

When I was using these tables believe me I didn’t had PC with me

but LOOKING DOWN the memory lane made made me realize that the teachers who taught me how to use these tables, PCs were no less than aliens to them. But WOW! they taught me something which I used 15 years after thinking I learnt something new

but in reality no. I merely used the same concept in a new design.