A Payroll Audit Needs 1 Awesome Formula (a creative dilemma)

A recent employee data audit left me with the need to build a formula that goes beyond a simple vlookup. The vlookup function (vertical lookup) has a limitation of only returning the first matching value in the vlookup dataset. The second limitation is that it keeps the data vertical and I needed the results to be returned horizontally. The horizontal data allows all the employee’s pertinent data to be in a single row that our database can read quickly. The employee data audit is a necessary process in order for the proper data to be uploaded into Gaus Systems. The below steps will allow you to write a formula that goes beyond vlookup.

My Dilemma

Every employee in the dataset had a single employee identification number. Every employee in the pension dataset had a single employee identification number but had multiple pension plans. This is how it looked (First Name, Last Name, Employee ID #, Pension plan name).

David| Gaus| 4356| Pension Plan 20.1%

David| Gaus| 4356| Pension Plan 1.9%

David| Gaus| 4356| Pension Plan 2.1%

Vlookup Works

Vlookup does work to return the first pension plan “Pension Plan 20.1%”, but it does not return the 2nd or 3rd pension plan names. This is where I wish Microsoft would have written a function that returns the 1st result, 2nd result, etc. Well, they did not write that function so we need to get creative.

An Array

From writing index & match formulas for salary schedules, I was familiar with the index function. Index returns a value in a table. I wanted to return the value of “Pension Plan Name”. =index(Pension Plan Name column)

Next, I used the SMALL function because it allows you to return the 2nd or 3rd value you designated in a table. From office.com (Microsoft’s website) I found this illustration. =SMALL(B2:B10,2) or return the 2nd smallest number in the B column.

Now, I need to write an IF statement to find the corresponding employee identification in my employee database to match the employee identification in the pension database. =if(Employee ID = An Employee ID in the Employee ID column, then find it in the ROW and add a 1 to it so that it is unique).

=index(Pension Plan Name Column,small(if(employee id = any employee id in this column,row(employee id column)-row(employee id column first cell)+1),2)) …then hit CTRL+SHIFT+ENTER.

The CSE function creates an array formula, and you can read more at office.com https://support.office.com/en-us/article/Create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d

Array Formula IF Statement

My final step was to write an IFERROR function in front of the index function. This told the formula that if an error occurs then return a blank “ “). This was the final formula result of my pension audit. =IFERROR(index(Pension Plan Name Column,small(if(employee id = any employee id in this column,row(employee id column)-row(employee id column first cell)+1),2)),” “)

This took a single employee identifier and returned each pension plan in its own unique column of data. Therefore, we took vertical data and made it horizontal which means that the entire row of data relates to a single employee and can be easily imported intoGausSystems.com.