The analyst's CORNER

Using Excel Lookup’s for survey submission job mapping

One of the challenges in preparing survey submission in Excel is mapping data from two different files to create a single, unified and consistent data submission. Combining stock grant data with your HRIS extract is one example. Mapping a list of matched survey jobs to individual incumbents in another. In both cases Excels’ Lookup function provides a quick and powerful solution.

The Excel Help file is typically cryptic when discussing the “Lookup” functions, sufficiently cryptic in fact to dissuade some from learning how to use it. However the lookup function is pretty easy to use and extremely powerful in creating efficiencies for data management.

The “Lookup” function enables users to use a formula to copy and paste values from another spreadsheet or Excel tab into the spreadsheet / tab you are working with AND ensure that the transferred data values line up in the correct rows and columns. The Lookup does this by comparing a “lookup” column in your current spreadsheet with the first column in the “target / source” spreadsheet. Once the target is mapped to the lookup column, any values in the target source can be transferred to your original spreadsheet merely by specifying the column location relative to the target. The Lookup function comes in two flavors, horizontal (target by rows) or vertical (target by columns). The VLookup (target by columns) function is the most useful for the HR applications we see.

The VLookup requires 4 inputs:

• Lookup value – this is the first value in the column in your spreadsheet that will be compared to the target column in the source spreadsheet. Fix the column location for the value by pressing the F4 button 3 times. If the first value in the lookup column is H7, pressing F4 will change the reference from H7 to $H$7 to H$7 to $H7. Setting an absolute column reference will allow you to copy the function equation to other columns in the same line.
• Table_array – this is the technical name for the list of fields you want to pull data from. Once you’ve mastered the Vlookup, you can pepper polite cocktail conversation with the word “table_array” to the delight and awe of your friends. Make sure to highlight all the columns and rows that serve as your source. Excel typically locks the reference using the $ signs (e.g. $H$6:$P$65) so you can copy the equation without losing the source location. Warning: make sure that the “table array” only has unique values in the target column, the first column of the array. If you have duplicates, it will upset the Vlookup function and give you, perhaps later, more stomach acid than you want.
• Index_num – this is literally the number of columns from the target source column. If the target source column is A and the value to copy is in column C, then the input value is 3. Always count the target column.
• Range_lookup – put in the word “False”. False means the match must be unique.. There are few circumstances in HR analysis where “False” is not the appropriate input. If you forget to put in False, you may get approximate rather than exact matches.

Once you set up the equation for the first value, if you set the absolute relationships correctly, you can double click on the bottom right corner of the cell and auto-fill the entire column. You can also copy/paste the original cell value in additional columns to copy/paste the data from the source worksheet.

Example

Employee job and pay data extracted from the Company HRIS system is displayed below. The stock grant data required for the Company’s survey submission is not available from the HRIS and must be merged with the extract spreadsheet. Not all employees received stock grants so sorting both files by the employee ID and copy / pasting all fields is not an option. The Vlookup function is used to query a spreadsheet containing the stock grant data and merge the grant information into the employee spreadsheet below.

The stock grant data spreadsheet, the source target, contains 3 employees who received stock grants. All the information necessary for submission is contained in this worksheet.

The Vlookup is setup in the first cell for merged information. In this case it’s the Grant Type (“NQSO”). The Vlookup equation for the first cell has the following form:

=Vlookup($A2,’GrantData’!$B$3:$F$5,2,false) where

• $A2 – cell / row for the employee ID in the Employee Data worksheet. This is the first “lookup” value. Notice that the cell has an absolute reference to column A. This reference won’t now change when this equation is copied to other cells to the right of the first Grant Type cell.
• ‘GrantData’!$B$3:$F$5 – Table array or in laymen’s speak, the group of columns you want to extract data from. The “GrantData” is the name of the worksheet / tab when the stock grant data is stored. Notice the absolute references for all columns and rows in the target / source spreadsheet. This enables the lookup equation to be copied without changing the source reference.
• 2 – the number of columns to the right of the target column, the Employee ID in the stock grant worksheet. The target column ALWAYS HAS TO BE THE FIRST COLUMN, and this 3 rd input is just the number of columns to the right.
• False – you want the function to only return values when the match of Employee ID is exact, else everyone in the Employee Data spreadsheet will be granted stock options and you will have egg on your face.

Process Steps

• Once the first cell equation is written, then copy / paste that equation to all cells in the first row of the Employee Data spreadsheet that should be filled with stock grant data IF IT EXISTS.
• Change the 3 rd input in each equation of the first row to correspond to the column in the Grant Data worksheet that should be merged. For instance, the “Shares Granted” is column 4.
• Highlight & copy the equations in the first row and then paste for the remaining rows in the Employee Data worksheet that should contain stock grant data

• The spreadsheet will now look like the example above, a spreadsheet with correct values in some cells and lots of annoying error messages, where no Employee ID match could be made, in the remaining cells. This is not a problem.
• After checking some of the cells to make sure the data merge is correct, copy all the data in the 4 Employee Data stock grant columns and then paste to the very same location using the “Paste Special” Edit option in Excel and select the “Values Only” option. This converts both the Vlookup equations and #N/A errors to values.
• Now highlight the cell headers for the 4 columns and use the Edit-Replace command to change “#N/A” to a blank value.

You have now merged the Grant Data for all your employees with their base and bonus data and done it in less time than it took to read this example. Now you are ready for the cocktail circuit and the sly references to “Table_Array”.