Category Archives: Formulae

How to communicate with Excel through formulae to make it do what you want.

XL FORMULA FOCUS: Redundancy calculations using INDEX and MATCH

Learn Index Match in Microsoft Excel

This post will walk you through how to calculate statutory (in the United Kingdom) redundancy payments. It’s a good example of how you can use the INDEX and MATCH excel functions together to find data in a table. The table is based on one from www.direct.gov.uk (so an official source and correct at time of posting) – if you need to use this for real then you should check that the table has not become out of date.

We’ve got a spreadsheet here to get you started:

You’ll see there are two tabs in the spreadsheet – Calculation and Table. The Calculation tab is where we’re going to create our formulas, which will refer to the redundancy table in the Table tab.

In the Calculation tab we’ve already got things started for you, but it will be up to you to enter the formulas as we go along. The orange cells just contain values, and the blue cells contain formulas. Three blue cells are marked with an XLCalibre X, and that’s where you’ll be creating your formulas.

According to the legislation you need certain information to calculate a redundancy payment: completed year’s of service, age and weekly pay (we have a great post here to help you use formulas to calculate length of service and age). You’ll see there are spaces to put these details in, though we’ve got some example already there. If you enter the annual salary, there’s a formula using the IF function to calculate the weekly salary (with a cap of £430 as per the legislation). We’re not going to worry about this, as we’re focusing on combining the INDEX and MATCH functions…

The INDEX function

Returns a value or reference of the cell at the intersection of a particular row and column in a given range

That’s what Excel says about INDEX. Essentially it means “In this array of cells, what do I find if I look X cells down and Y cells across”. The function is structured like this:

=INDEX(Array, Row number, Column Number)

With the redundancy table we have a perfect example of an array that would make INDEX useful. We need to look down a number of rows to find the age, and then across to find the length of service. If only we knew how to tell Excel how many rows down and columns across it needs to look…

The MATCH function

Returns the relative position of an item in an array that matches a specified value in a specified order

=MATCH(Lookup Value, Lookup Array, Match Type)

So we can use MATCH to find how many rows, and then again to find how many columns. So our final formula will have this structure:

=INDEX(Array, MATCH(Lookup Value, Lookup Array, Match Type), MATCH(Lookup Value, Lookup Array, Match Type))

If that looks complicated don’t worry. We’re going to break it down starting with the first MATCH formula.

MATCH

Our first MATCH will be to find the number of rows down our table to look according to the person’s age. We’re going to put it in cell B10 of our spreadsheet. Start by putting typing

=MATCH(

The first thing we need is the lookup value (i.e. the age we’re going to find). I’ve already put this in cell B8, so we’re just going to point our formula there then add a comma. This means we can just change B8 if we want to change the age lookup value, rather than amending the formula. So we should now be here:

=MATCH(B8,

Now we need to enter the lookup array, which is the list of ages in our Table tab. You can just flick to that tab and select cells A4:A48, then press comma:

=MATCH(B8,Table!A4:A48,

We’re pretty much there now. The final part of the MATCH is called match type, and we are just going to enter 0 and close the bracket of the formula. 0 means that we want the function to find only an exact match for our lookup value, which is what is called for in this case. So here we are:

=MATCH(B8,Table!A4:A48,0)

This formula tells us how many rows down our age range our look up value in cell B8 is, in this case 24.

We now do the same with another MATCH to find the number of columns across to look for length of service. Try it yourself in cell B12, following and adapting the steps we’ve just gone through… …

You should finish with this formula:

=MATCH(B6,Table!B2:U2,0)

INDEX

=INDEX(Array, Row number, Column Number)

We already have our row number and column number from our MATCH formulas, so we can simply refer to those cells like this:

=INDEX(Array, B10, B12)

And now we just need the array that our INDEX is looking at. The reference will be as high and low as the reference in our row MATCH (from 4 to 48) and as far left and right as our column MATCH (from B to U). So the resulting array is B4:U48. Here it is in situ:

=INDEX(Table!B4:U48,B10,B12)

There you have it, your calculation should now work and you can change the age and length of service to your heart’s content. We’ve added a payment calculation so you can see the final result of the calculation.

If you want to go the extra mile and tidy things up you could put your MATCH formulas into your INDEX like so:

=INDEX(Table!B4:U48,MATCH(B8,Table!A4:A48,0),MATCH(B6,Table!B2:U2,0))

And this is now your bona fide INDEX MATCH combo. It may take you a few practices before you remember it every time, but it’ll be well worth the effort.

UPDATE: Now that you’ve learned how to combine INDEX and MATCH, check out this Gantt chart to see another example of how it can be used. Be warned, it’s part of a much bigger formula…

XL FORMULA FOCUS: Need to calculate length of service or age? Use YEARFRAC

Length of service is used all the time in HR – for example long service awards or calculating redundancy payments. Those of you fortunate enough to have a solid HR system in place will hopefully have some automation to keep you aware of upcoming key dates, but for others Excel can be the next best thing.

We will be using the YEARFRAC function:

YEARFRAC(start_date, end_date, [basis])

 

Let’s do an example…

1. In cell A1, enter your date of birth (for arguments sake use the format dd/mm/yyyy).

2. In cell B1, enter  =TODAY()  . This will return today’s date, which will update automatically as time goes on.

3. In cell C1, enter  =YEARFRAC(A1,B1,1)  .

 

The number one at the end of the formula is what is referred to above as the ‘basis’. It tells Excel on what basis to calculate the difference between start_date and end_date. Personally I’ve always used 1, which is described as Actual, and it has always been accurate and fit for purpose. If you’re unsure, while working on the YEARFRAC formula hit the fx button next to the formula bar, and then select ‘help on this function’. It will give you a bit more information about the YEARFRAC function and detail the various basis options.

 

So, cell C1 should now show you the number of years between your start date and end date (in this case how old you are today). Depending on how you format this cell (and assuming its not your birthday – if it is happy birthday!) you will probably have a number of decimal places shown. This is fine if you want a very precise figure, but in many practical applications you will want completed years (e.g. I am 30 years old, not 30.153663 years old). So for this we’re going to use the INT function (short for Integer).

 

4. In cell D1, enter INT(C1)  .

 

You will now have your age shown as an integer.

If you wanted to you could condense this into fewer cells.

 

5. Select cells B1, C1 and D1 and press delete.

6. In cell B1, enter this:

=INT(YEARFRAC(A1,TODAY(),1))

You can now use this formula repeatedly, changing the contents of cell A1 to any date that you want.

If you’ve made it this far Well Done! You’ve used 3 different functions in one formula to give a practical output.

If you liked that, here’s a post about EDATE, a really straightforward date function that I use to calculate the of probation periods or notice periods.