Excel named ranges and labels

Excel labels can be used to perform a quick lookup on a table.

They are similar to names / named ranges but provide different functionality.

Names can refer to a range (A1:B3) or number of ranges(A1:B3, G5:J10), or a formula($A$1*52*7) or an array({0;1;2;3;4}).

Before I explain how to use labels, we must first enable them: Tools>Options...>Calculation tab>Enable: 'Accept labels in formulas' checkbox.

Click OK and then in cell B1 Type: 'Jan'. Fill right horizontally Until 'Jul'.
In cell A2 type 'North'. In cell A3 type 'East', A4 'South', A5 'West'.

You should now be able to see a table format. Now we need some data. So we will use the random function to generate this. In cell B2 type in the formula (without quotes): '=Int(Rand()*1000)'. Fill right until Jul, and then down to West. Now copy the cells B2:H5 and paste values to remove the formulas.

Assuming this data refers to some sales figures for the Months Jan-Jul for the four regions shown, we can perform a lookup on the table and find the relevant sales figure at the intersection of a particular combination of Month/Region.

To perform the lookup, we can enter the following formula in any blank cell:

To lookup the sales figure in May for the South region, all we have to do in enter (without quotes) '=May South' or '=South May'.

You can check that this value is correct.

Before i discovered this jem, I used to use a nested match/offset function...

This simple example can be extended to any number of applications, and make your job as an excel user much easier.

Well, this is my first blog...

more later...