PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES
CONTENTS
10 VLOOKUP EXAMPLES IN EXCEL-PART I
INTRODUCTION
REVISION:
SYNTAX: VLOOKUP
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.
EXAMPLE 1:SIMPLE LOOKING UP A VALUE IN A TABLE USING VLOOKUP
PROBLEM:
FIND OUT THE SALARY AND AGE OF WILLIAM FROM THE GIVEN TABLE. CURRENT DATE IS 30.03.2020
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 |
SOLUTION: EXAMPLE 1
FIND OUT THE SALARY AND AGE OF WILLIAM FROM THE GIVEN TABLE.
PLANNING THE SOLUTION
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.
STEPS TO SOLUTION [EXAMPLE 1]
Let us find the salary first. (THIS IS THE USAGE OF SIMPLE VLOOKUP)
- Put the following formula in J16 (You can put the formula in any cell) =VLOOKUP(“WILIAM”,H7:L13,4,FALSE)
- WILIAM is the name of the employee which we want to find the data for.
- H7:L13 is the table in which we’ll do the finding.
- 4 is the column index number of the salary.
- R3RY is the exact match as matching should be exact only. The TRUE for the formula will be tested in further examples.
- Apply the following formula in J18 as =LEFT(YEARFRAC(VLOOKUP(“WILIAM”,H7:J13,3,FALSE),DATE(2020,3,30),1),2)
LET US UNDERSTAND THE FORMULA
- LEFT is the function at the top level , which will take only 2 digits from the left of the final output.
- Next level is the function YEARFRAC.
- Second argument of the function is already present as the date. But the first date has to be found using the VLOOKUP.
- So we applied vlookup in the yearfrac as VLOOKUP(“WILIAM”,H7:J13,3,FALSE). It’ll return the date of birth as date.
- Next function used is DATE(2020,3,30) which is needed for the input of the YEARFRAC FUNCTION else it’ll return an error.
- DAYS CALCULATION will be as actual so given code is 1.
- The answer is as 28.
- So we have solved the problem using VLOOKUP.
EXAMPLE 2:SIMPLE LOOKING UP A VALUE IN A TABLE USING VLOOKUP
PROBLEM:
FIND OUT THE NAME OF THE EMPLOYEE WITH THE HIGHEST SALARY IN THE GIVEN TABLE
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 |
SOLUTION: EXAMPLE 2
FIND OUT THE NAME OF THE EMPLOYEE WITH THE HIGHEST SALARY IN THE GIVEN TABLE
PLANNING THE SOLUTION
STEPS TO SOLUTION [EXAMPLE 2]
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:
- Put this function in cell J44.
=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:
- Put the formula in J45
=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:
- Put the following function in J46
=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.
EXAMPLE 3:USE OF APPROXIMATE MATCH
PROBLEM:
FIND THE EXPERIENCE OF PERSON WHO HAS SALARY AROUND 80000.
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 |
SOLUTION: EXAMPLE 3
FIND THE EXPERIENCE OF PERSON WHO HAS SALARY AROUND 80000.
PLANNING THE SOLUTION
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.
STEPS TO SOLUTION [EXAMPLE 3]
Let us find the salary first.
- Put the following formula in K67 (You can put the formula in any cell)
- =VLOOKUP(80000,K58:L64,2,TRUE)
- 80000 IS THE VALUE TO BE LOOKED UP
- K58:L64 IS THE LOOKUP TABLE AS WE NEED TO MATCH THE SALARY AND RETURN THE VALUE FROM THE EXPERIENCE.
- 2 IS THE COLUMN INDEX NUMBER FROM WHERE THE VALUE IS TO BE RETURNED.
- TRUE IS THE APPROXIMATE MATCH WHICH WORKED IN THIS CASE AS 80000 WAS NOT AVAILABLE SO IT RETURNED 75000 AND GAVE THE RESULT AS 9 YEARS WHICH IS THE EXPERIENCE OF THE PERSON WITH 75000 AS SALARY.
EXAMPLE 4:JOINING TWO TABLES USING VLOOKUP
PROBLEM:
SOME MORE DETAILS ABOUT THE SAME EMPLOYEES HAVE COME. PREPARE A FINAL TABLE COMPRISING OF ALL DETAILS IN A SINGLE REPORT.
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 |
SOLUTION: EXAMPLE 4
SOME MORE DETAILS ABOUT THE SAME EMPLOYEES HAVE COME. PREPARE A FINAL TABLE COMPRISING OF ALL DETAILS IN A SINGLE REPORT.
PLANNING THE SOLUTION
STEPS TO SOLUTION [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.
- Put the column names in the TABLE 1 at M78 and N78 as given in the following figure.
- In M79 put the following formula
- =VLOOKUP(H79,P79:Q85,2,FALSE)
- H79 is the first name, MICHAEL which we will find in TABLE 2, P79:Q85 is the lookup table, 2 is the column index number of value return , false is exact match.
- The value returned is Y.
- But we have to find the values for all the list so drag the formula. But before that we need to fix the dimensions of our lookup table so that it doesn’t go relative when we drag fill the formula. So for that we need to put a $ cell before row and column in the range lookup table in the formula. Its discussed in details here.
- Now the formula becomes
- =VLOOKUP(H79,$P$79:$Q$85,2,FALSE)
- Drag the formula upto the last row.
- Repeat the process in column N79.
- PUT this formula in N79
- =VLOOKUP(H79,$P$79:$R$85,3,FALSE)
- Drag the formula upto the last row.
- Following animated image shows the process.
EXAMPLE 5:JOINING TWO TABLES WITH SAME FIELDS USING VLOOKUP
PROBLEM:
SOME MORE DETAILS ABOUT THE SAME EMPLOYEES HAVE COME. PREPARE A FINAL TABLE COMPRISING OF ALL DETAILS IN A SINGLE REPORT. MIND IT, THERE ARE TWO EMPLOYEES WITH THE SAME NAME.( TAKE CARE PLEASE)
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 |
SOLUTION: EXAMPLE 5
SOME MORE DETAILS ABOUT THE SAME EMPLOYEES HAVE COME. PREPARE A FINAL TABLE COMPRISING OF ALL DETAILS IN A SINGLE REPORT.MIND IT, THERE ARE TWO EMPLOYEES WITH THE SAME NAME.( TAKE CARE PLEASE)
PLANNING THE SOLUTION
STEPS TO SOLUTION [EXAMPLE 4]
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.
- Put the column names in the TABLE 1 at M97 and N97.
- In M98 put the following formula
- =VLOOKUP(G98,P98:S104,3,FALSE)
- G98 is the check field of table 1, which we will find in TABLE 2, P98:S104 is the lookup table, 3 is the column index number of value return , false is exact match.
- The value returned is Y.
- But we have to find the values for all the list so drag the formula. But before that we need to fix the dimensions of our lookup table so that it doesn’t go relative when we drag fill the formula. So for that we need to put a $ cell before row and column in the range lookup table in the formula. Its discussed in details here.
- =VLOOKUP(G98,$P$98:$S$104,3,FALSE)
- Drag the formula upto the last row.
- Repeat the process in column N79.
- PUT this formula in N79
- =VLOOKUP(H79,$P$79:$R$85,3,FALSE)
- Drag the formula upto the last row.
- The final results are here.
OTHER WAYS TO REACH THIS ARTICLE
WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.