Today I will be looking at the vlookup function within Excel and how this often leads to issues in the spreadsheet if the user does not understand some of its key limitations. The vlookup function is a powerful feature enabling data to be merged from one data source to another. It is widely used, however, in the hands of the unwary it can lead to significant issues.
The vlookup function “looks up” a value in another table and returns a result into the relevant cell. I will be considering two challenges that are often faced:
Issue 1 – Using vlookup on a changing Excel source table
The match can either be an exact one or a nearest allowing considerable flexibility. The following scenario will clarify a typical use for the vlookup function.
I have one table that has a set of staff grades with their standard cost rates and I have another table with all my staff and I want to apply the standard cost rates to the staff table. For ease of understanding the formula I have named the grade column in the staff table as “Grade” and named the Cost table “Costs”.
By looking at the formula you can see what vlookup is doing. It is “looking” grade in the table costs and returning the entry in the second column (designated by the “2” in the vlookup formula). Herein lies the problem in that this is a fixed reference. To illustrate the problem if someone now inserts a new column into the Costs table for example chargeout rate the staff table will display the incorrect result as it continues to read the 2nd column which is now charge out rate. Someone needs to update the staff table to reflect this change and change the “2” to “3”. In my experience this can often be forgotten particularly for the less sophisticated user. This can be overcome by using the more complicated “Match” and “Index” functions but this again assumes that the user is an experienced excel user who understand how to use this functionality.
OrgVue deals with this issue effectively as it uses column heading to “Match” the data required. It therefore does not require detailed understanding to merge data and it is easy to use.
In addition you are able to use the “undo” function before you commit the changes to the database.
Issue 2 – Merging data from different excel sources to the same excel sheet
I have a spreadsheet with staff details and I have another spreadsheet(s) with the number of days sickness that staff have taken during a period (possibly as a result of the records being maintained by different managers). More often than not this will be a subset of the full employee list (it is unlikely that all staff are sick during a month). Using the vlookup function to merge this data from a single source is an easy process as follows:
However, you will notice that an error is displayed #N/A. This of course can be corrected by using a more complicated formula (iserror) but this is beginning to be more advanced. Furthermore, if there are two data sources providing input to the same column e.g.
Excel needs to have a far more complicated formula to combine them successfully. Often it maybe necessary to combine the underlying data before bringing into the centralise sheet. This can be time consuming and lead to inaccurate data transfers.
OrgVue handles this type of complexity and allows multiple data sources to be merged into a single central data repository.
This functionality is very powerful for combining data and using the strengths of OrgVue it is possible to select and review the changes that have been made before they are committed to the database. It is easy to get started and you will get value faster than believed possible.
Find out more about this amazing tool at OrgVue.com.
Latest posts by Patrick Gracey (see all)
- “Just a little faster this time”: How HR thinking needs to evolve to help organisations survive - October 1, 2013
- Fortune Global 500 – Adding insight - August 8, 2013
- What can be done to improve your chances of acquisition success - June 27, 2013