PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
The syntax ( the way how formula is phrased for excel) of VLOOKUP is
=VLOOKUP(cell address of value to be matched, range of cells to search, column number to return the value, match should be approximate or exact)
So, a sample format is here. Suppose the value to be found is in cell H13 and the table from which the value is to be extracted has the range I12:K25 (3 columns) and the match is to be exact.The format in the output cell will be
=vlookup(H13,I12:K25,3,false)
This will find out the value of H13 in the table I12:K25 and return the value of third column i.e. K if it could find H13 in the table’s first column.
The table is given below. There are a few details given about the employees. (The table can be copied and pasted in Excel).
Kindly paste it at H6. NAME SHOULD COME AT H6, So that the solution matches with yours.
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
WILIAM | [email protected] | 30.6.1991 | 75000 | 9 |
We have to find the salary and age of WILLIAM.
For the salary, we’ll use VLOOKUP simply and lookup the salary of WILLIAM.
For the age, we need to find out the duration of current date from the DATE OF BIRTH as age is not available directly. So let us start working towards the solution.
For working out the days from the date of birth to current date we’ll use the function
We’ll be using YEARFRAC function for the same.
The Syntax of the function is
=YEARFRAC(STARTING DATE, ENDING DATE, DAY COUNT BASIS)
It’ll return us the age in decimal but we can change the format of the cell to FRACTION or we can trim the
year portion from the age as we just need the age.
Let us find the salary first. (THIS IS THE USAGE OF SIMPLE VLOOKUP)
LET US UNDERSTAND THE FORMULA
The table is given below. There are a few details given about the employees. (The table can be copied and pasted in Excel).
Kindly paste it at H6. NAME SHOULD COME AT H6, So that the solution matches with yours.
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
WILIAM | [email protected] | 30.6.1991 | 75000 | 9 |
Let us find the salary first.
The solution in the picture shown below is given in three steps.
1.USAGE OF LARGE FUNCTION.
USAGE OF VLOOKUP
USAGE OF NESTED FUNCTIONS.
LARGE FUNCTION:
=LARGE(K35:K41,1)
K35:K41 is the array containing the salaries.
1 is the highest number in the array.Second highest is 2 and so on. The result is 98000.
2. USAGE OF VLOOKUP:
=VLOOKUP(98000,K35:M41,3,FALSE)
where 98000 is the highest salary, K35:M41 is the table to be looked at, 3 is the column index number to be returned, false is exact match.
*You must be wondering from where the column M came. As you can see its written in the picture that we had to copy this column. That is the problem with the VLOOKUP as we can compare only the first column. So when we need to compare the SALARY and return the NAME, SALARY needs to be the first column in the lookup table and NAME COLUMN needs to be to the right of the first column of the lookup table. That is why this arrangement needed to be done. Although its not that hard, we just need to copy the column.
3. NESTED FUNCTION:
=VLOOKUP(LARGE(K35:K41,1),K35:M41,3,FALSE)
VLOOKUP is the outer function, first argument will be returned by LARGE function , so its put in place of first argument.
Second argument is the lookup table.
Third argument is the column index of the selected lookup table i.e. 3 as the NAME column is at position 3.
FALSE is the exact match.
Problem Solved.
The table is given below. There are a few details given about the employees. (The table can be copied and pasted in Excel).
Kindly paste it at H6. NAME SHOULD COME AT H6, So that the solution matches with yours.
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
WILIAM | [email protected] | 30.6.1991 | 75000 | 9 |
This is a simple problem having only one step and the usage of a single function VLOOKUP.
One main point to be noticed here is that there is no employee whose salary is exactly 80000. So
we’ll make use of APPROXIMATE MATCH.
APPROXIMATE MATCH:If enabled, will find out the available match or the next lesser value than the value to be found.
So let us apply the formula.
Let us find the salary first.
The table is given below. There are a few details given about the employees. (The table can be copied and pasted in Excel).
Kindly paste it at H78. NAME SHOULD COME AT H6, So that the solution matches with yours.
TABLE 1
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
WILIAM |
| 30.6.1991 | 75000 | 9 |
The second table of details received is given below. There are few more details given about the employees. (The table can be copied and pasted in Excel).
Kindly paste it at P78. NAME SHOULD COME AT P78, So that the solution matches with yours.
TABLE 2
NAME | MARITAL STATUS | LOCATION |
MICHAEL | Y | NEWYORK |
RAVI | Y | DELHI |
DANIEL | N | MOSCOW |
FLORENCE | N | PARIS |
TOM | Y | MUMBAI |
HARRY | N | LONDON |
WILIAM | N | CHICAGO |
Let us copy first column to the table 1. i.e. MARITAL STATUS.
NOTE: Although the names of both the tables are in the same sequence but VLOOKUP doesn’t care about that and it can handle any sequence but they should be unique. i.e. no repetition of the unique identifier.
The table is given below. There are a few details given about the employees. (The table can be copied and pasted in Excel).
Kindly paste it at H78. NAME SHOULD COME AT H6, So that the solution matches with yours.
TABLE 1
NAME | EMAIL ID | DOB | SALARY | EXPERIENCE |
MICHAEL | [email protected] | 12.02.1990 | 12000 | 10 |
RAVI | [email protected] | 15.03.1991 | 10000 | 1 |
DANIEL | [email protected] | 14.05.2013 | 30000 | 2 |
FLORENCE | [email protected] | 18.06.2000 | 45000 | 5 |
TOM | [email protected] | 25.07.2001 | 98000 | 4 |
HARRY | [email protected] | 31.7.1993 | 65000 | 6 |
WILIAM |
| 30.6.1991 | 75000 | 9 |
The second table of details received is given below. There are few more details given about the employees. (The table can be copied and pasted in Excel).
Kindly paste it at P78. NAME SHOULD COME AT P78, So that the solution matches with yours.
TABLE 2
NAME | MARITAL STATUS | LOCATION | DOB |
MICHAEL | Y | NEWYORK | 12.02.1990 |
DANIEL | N | MOSCOW | 14.05.2013 |
FLORENCE | N | PARIS | 18.06.2000 |
TOM | Y | MUMBAI | 25.07.2001 |
HARRY | N | LONDON | 31.07.1993 |
WILIAM | N | CHICAGO | 30.06.1991 |
FLORENCE | Y | DELHI | 20.06.1991 |
Let us create a unique field by combining NAME AND DOB.
So we need to create a column named CHECK in both the tables by using the formula
IN G98
=CONCATENATE(H98,J98)
and drag down the formula for the complete column or double click the small square in the right lower corner of the active cell selector.
and
In P98
=CONCATENATE(Q98,T98)
and drag down the formula for the complete column or double click the small square in the right lower corner for auto fill the column if you don’t wanna drag.
After the unique fields have been created , the rest process is like EXAMPLE 4.
Let us copy first column to the table 1. i.e. MARITAL STATUS.
NOTE: Although the names of both the tables are in the same sequence but VLOOKUP doesn’t care about that and it can handle any sequence but they should be unique. i.e. no repetition of the unique identifier.
OTHER WAYS TO REACH THIS ARTICLE
YOU MAY LIKE