Tag Archives: if

Splitting names

Today on LinkedIn somebody posed the following question (for anonymity I have changed any names given):

Sorting by Last Name

I have a list of 2,000 names by First, Middle, Last, Suffix (Jr or I,II, or III) I need to be able to Sort by last name. I know how to use “Text to Column” but unfortunately there is not a consistancy in the names, I have some that have just First and Last name, I have some that may have a space in their last name such as “De Souza” or “John Dave Jones Jr II” any suggestions.

The issue wasn’t really about sorting, which would be easy with autofilters (CTRL+Shift+L to apply autofilters). The problem was separating out the parts of the names, and lining them up in the right columns. Usually text to columns would fix this, but doing it with the different formats mentioned in the post would have left last names in various different columns.

I find this is a common predicament in HR, where lists of names pulled from various sources are full of inconsistencies, and therefore hard to manipulate or ‘plug in’ to other systems or spreadsheets.

My solution was not particularly elegant. You are rarely afforded the time to create the perfect single formula answer that covers all angles. But it does show you some text-manipulation functions at work.

I e-mailed the file to the poster. They mentioned 5 exceptions that it had not worked for, and they all included initials (I hadn’t thought of this possibility). This leads to my next point: testing. If at all possible you should use your spreadsheets from beginning to end with real data to check if they’re working before they go to your customer. Even better, get someone else to do this for you, as its easy to miss something when you’re buried in details.

Here you can access the spreadsheet that I created. See if you can understand how it works. How would you do it differently? Can you adjust it to work for with initials? Feel free to send your spreadsheets in to admin@xlcalibre.com.

If you’d be interested in similar discussions, come join the brand new XLCalibre Group on LinkedIn.

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…