We are essentially building a tool that can look for an employee and return his or her salary (don’t worry, it sounds harder than it actually is).
This tool needs to be placed somewhere in an Excel sheet.
I recommend creating a defined area where I can select the different criteria and type the formula.
To do this, I simply select range I2:J4 and pick “Thick Outside Border” by clicking the little arrow next to the border button in the “Font” group in the “Home tab”.
After this, I mark where I want my criteria and where the formula goes by writing “Last name” in I2, “Date of birth” in I3 and “Salary” in i4.
Additionally, I hold the left mouse button down on the line between the column names and drag to make them wider, so that the entire content of the cell fits.
Why “Last name” and “Date of birth”?
You might recall that I told you that any lookup function needs to look for a unique piece of information.
Unfortunately, none of the information in the employee database is unique, not even last name or date of birth.
However combining last name AND date of birth greatly increases the chances of finding a unique value.
If we combine “First name” and “Last name” we might end up with a David Jones.
However, if we combine “Last name” and “Date of birth” we would get Jones 07-23-1991. There are two David Jones’ in our data, but only one employee with last name Jones and birthdate 07-23-1991.
So combining last name and date of birth is the smarter choice as this creates a unique identifier in most cases.
“But why even bother combining information in the database?”
ANY look up function needs a unique piece of information to work.
In this case, the database didn’t contain any sort of unique identifier or information.
We, therefore, went ahead and created a unique identifier ourselves by using different criteria, in order to create something unique to look for.
It’s like we tell Excel: “Look for an employee with last name Jones who is born 07-23-1991 and tell me that person’s salary.”