The analyst's Excel: join it, summarise it, check it

Excel is widely used for actuarial analysis and modelling. This course focuses on the parts that are most useful when you are given data, asked to produce an answer and expected to show that the answer can be checked.

The level this module covers

This is not a start-from-zero Excel course. We assume you can enter a formula, work with cells and ranges, copy a formula and use basic arithmetic and functions such as SUM.

These six lessons cover lookups, absolute references, data checks, conditional summaries, weighted averages, model structure, reconciliations, discounting and reviewing somebody else's work.

By the end, you should be able to take a small actuarial dataset, join and summarise it, identify common data problems, build a simple model with visible inputs and checks, and review a workbook before it is relied on. We are not trying to cover every Excel feature, VBA or advanced automation.

What is a lookup?

A common Excel task is taking a value in one table and using it to find related information in another.

Suppose one table contains:

You have the policy number P102 and want to know which customer it belongs to.

A lookup searches the policy column for P102 and returns the corresponding value from the customer column: Ben.

Excel gives you several ways to do this. The three you will meet in this lesson are:

• XLOOKUP, the modern approach
• VLOOKUP, an older function you will still encounter
• INDEX and MATCH, two functions that can be combined to perform more flexible lookups

We will cover the useful differences between them before using them on a larger actuarial dataset.

XLOOKUP: the modern approach

The basic XLOOKUP formula has three jobs:

=XLOOKUP(what_to_find, where_to_find_it, what_to_return)

Using our policy table, imagine the policy number we want to find is in cell E2, the policy numbers are in A2:A3 and the customer names are in B2:B3.

We could write:

=XLOOKUP(E2,A2:A3,B2:B3)

Read that from left to right:

• E2 is the value we are looking for
• A2:A3 is where Excel should look for it
• B2:B3 is where Excel should return the answer from

If E2 contains P102, the formula returns Ben.

XLOOKUP looks for an exact match by default. You can also tell it what to display if nothing is found:

=XLOOKUP(E2,A2:A3,B2:B3,"NOT FOUND")

Now a policy number that is missing from the table returns NOT FOUND instead of #N/A.

That is the basic pattern to remember:

find this → in this column → return the corresponding value from this column.

VLOOKUP: the older version

VLOOKUP does the same basic job, but its formula is arranged differently:

=VLOOKUP(what_to_find, table, column_number, exact_or_approximate)

Using the same policy example:

=VLOOKUP(E2,A2:B3,2,FALSE)

This tells Excel to:

• find the value in E2
• search the first column of A2:B3
• return the value from the second column of that table
• use an exact match because the final argument is FALSE

So if E2 contains P102, the result is again Ben.

VLOOKUP still appears in old workbooks and in the IFoA's published Excel guidance, so it is worth understanding. It also has some weaknesses that explain why XLOOKUP is usually preferable in a modern workbook.

Where VLOOKUP can go wrong

It uses approximate matching if you omit the final argument.

=VLOOKUP(E2,A2:B3,2)

does not mean the same thing as:

=VLOOKUP(E2,A2:B3,2,FALSE)

Without the final argument, VLOOKUP assumes an approximate match. That can be useful for properly sorted banded data, but dangerous when you expect an exact policy number or customer ID.

It can only return values to the right.

VLOOKUP searches the first column of the table you give it and returns values from columns to its right. If the value you want to return sits to the left of the lookup column, VLOOKUP cannot do the job directly.

It refers to the return column using a number.

In:

=VLOOKUP(E2,A2:C10,2,FALSE)

the 2 means "return the second column of the table".

If someone inserts another column inside that table, a formula built around a fixed column number can return the wrong information. XLOOKUP avoids this by specifying the lookup and return ranges separately.

INDEX and MATCH: splitting the lookup into two jobs

INDEX and MATCH can be combined to do the same job as a lookup.

MATCH finds where something is.

Suppose the policy numbers are in A2:A3:

=MATCH("P102",A2:A3,0)

returns 2, because P102 is the second item in that range.

The final 0 tells MATCH to look for an exact match.

INDEX returns the value at a particular position in a range:

=INDEX(B2:B3,2)

returns Ben, because Ben is the second item in B2:B3.

Put the two together:

=INDEX(B2:B3,MATCH(E2,A2:A3,0))

MATCH first finds which row contains the policy number in E2. INDEX then returns the customer from the same position in the customer column.

Unlike VLOOKUP, the return column does not have to sit to the right of the lookup column.

Exact and approximate MATCH

The final argument of MATCH controls how it searches.

0 means exact match.

1 means find the largest value that is less than or equal to the value being searched for. For this to work correctly, the lookup values must be sorted in ascending order.

That second version is useful for banded tables.

Suppose age bands begin at:

17, 25, 40, 60

A driver aged 34 belongs in the band beginning at 25. MATCH with a match type of 1 finds that band even though the number 34 does not appear in the table.

You will use this in the exercise below.

Locking a range with dollar signs

Excel normally changes cell references when you copy a formula.

If a formula in row 2 refers to E2 and you copy it down one row, Excel changes the reference to E3. Usually that is exactly what you want.

But some ranges should stay fixed.

Suppose your rate table is in A2:B5 and you are copying a lookup down hundreds of policy rows. You do not want the rate table to move from A2:B5 to A3:B6, then A4:B7 and so on.

Dollar signs make a reference absolute:

$A$2:$A$5

That range stays fixed when the formula is copied.

So in the exercise below:

=INDEX($A$2:$A$5,MATCH(E2,$B$2:$B$5,1))

the rate card stays in the same place while you copy the formula down the policy rows. The rate-card ranges therefore stay fixed, while the policy reference changes from E2 to E3, E4 and so on.

You do not need to type the dollar signs manually. While editing a formula, select a cell reference and press F4 to cycle through the different reference types:

• A1 - row and column can both move
• $A$1 - row and column are both fixed
• A$1 - row is fixed
• $A1 - column is fixed

On some laptops you may need to press Fn + F4.

For the exercise below, you want the rate-card ranges fully fixed, so $A$2:$A$5 and $B$2:$B$5 stay in place when the formula is copied down.

Summarising with a condition

Once you have joined information onto the data, a common next step is to summarise it.

SUMIF adds values where one condition is met:

=SUMIF(range_to_check, condition, range_to_sum)

Suppose regions are in F2:F7 and premiums are in G2:G7. To calculate the total premium for London:

=SUMIF(F2:F7,"London",G2:G7)

Excel checks the region on each row and adds the corresponding premium where the region is London.

Later in this module we will use SUMIFS and other approaches when more than one condition is involved.

COUNT, COUNTA and a simple check

A good spreadsheet does not just calculate an answer. It checks that the calculation has worked.

COUNT counts cells containing numbers:

=COUNT(G2:G7)

COUNTA counts cells that are not empty:

=COUNTA(D2:D7)

Suppose there are six policy numbers in D2:D7 but one lookup failed and only five premiums were returned in G2:G7. COUNTA returns 6 while COUNT returns 5.

You can turn that into a visible check:

=IF(COUNT(G2:G7)=COUNTA(D2:D7),"OK","CHECK")

This does not prove every premium is correct. It checks that every policy received a numeric premium.

Build the join

Now put those pieces together in the exercise below.

You have a rate card containing the base premium for four age bands and a separate list of six policies. Your job is to attach the correct premium to each policy, summarise the result and build a check that every policy was rated.

The rate card is deliberately arranged with the premium to the left of the age band. That means VLOOKUP cannot solve the lookup directly, so you will use INDEX and MATCH.

Reconcile before you send

A useful habit is to record the size of the data you received and check that the result still accounts for it.

Here, six policies went in and six received a premium. The regional figures should also add back to the £3,920 whole-book total.

Six rows can be checked by eye. Forty thousand cannot. The checks you build into the workbook are what make the same process reliable when the data gets larger.