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

FOR THE LEARNING VLOOKUP CLICK HERE.(FOR VLOOKUP IN HINDI CLICK HERE).
THE EXAMPLES ARE GIVEN IN A PROBLEM SOLUTION FORMAT. TRY YOURSELF AND THEN LOOK AT THE SOLUTION.

 

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.

NAMEEMAIL IDDOBSALARYEXPERIENCE
MICHAEL[email protected]12.02.19901200010
RAVI[email protected]15.03.1991100001
DANIEL[email protected]14.05.2013300002
FLORENCE[email protected]18.06.2000450005
TOM[email protected]25.07.2001980004
HARRY[email protected]31.7.1993650006
WILIAM[email protected]30.6.1991750009

 

VLOOKUP EXAMPLES SAMPLE TABLE
EXCEL:VLOOKUP:EXAMPLE 1 DATA SAMPLE

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.
SIMPLE VLOOKUP EXAMPLE
EXCEL:VLOOKUP:EXAMPLE 1:RESULT
After the first part of the solution let us try to solve the second part.
For that we’ll find out the date of birth by using Vlookup and then apply the value in the YEARFRAC function.
So for that
  • 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.
VLOOKUP RESULT 2
EXCEL:VLOOKUP:EXAMPLE 1:RESULT 2

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.

NAMEEMAIL IDDOBSALARYEXPERIENCE
MICHAEL[email protected]12.02.19901200010
RAVI[email protected]15.03.1991100001
DANIEL[email protected]14.05.2013300002
FLORENCE[email protected]18.06.2000450005
TOM[email protected]25.07.2001980004
HARRY[email protected]31.7.1993650006
WILIAM[email protected]30.6.1991750009

 

VLOOKUP EXAMPLES SAMPLE TABLE
EXCEL:VLOOKUP:EXAMPLE 2 DATA SAMPLE

SOLUTION: EXAMPLE 2

FIND OUT THE NAME OF THE EMPLOYEE WITH THE HIGHEST SALARY IN THE GIVEN TABLE

PLANNING THE SOLUTION

We have two parts in this question.
1. FINDING THE MAX SALARY
2. FINDING OUT THE PERSON NAME WITH THE MAX SALARY.
For finding out the max salary, we can use the formula LARGE( ARRAY,POSITION FROM THE LARGEST).
3. After finding out the largest salary, we’ll find out the person corresponding to this salary.

 

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.

EXCEL:VLOOKUP:EXAMPLE 2:RESULT

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.

NAMEEMAIL IDDOBSALARYEXPERIENCE
MICHAEL[email protected]12.02.19901200010
RAVI[email protected]15.03.1991100001
DANIEL[email protected]14.05.2013300002
FLORENCE[email protected]18.06.2000450005
TOM[email protected]25.07.2001980004
HARRY[email protected]31.7.1993650006
WILIAM[email protected]30.6.1991750009

 

VLOOKUP EXAMPLES SAMPLE TABLE
EXCEL:VLOOKUP:EXAMPLE 3 DATA SAMPLE

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.
GYANKOSH VLOOKUP EXAMPLE 3
EXCEL:VLOOKUP:EXAMPLE 3:RESULT

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

NAMEEMAIL IDDOBSALARYEXPERIENCE
MICHAEL[email protected]12.02.19901200010
RAVI[email protected]15.03.1991100001
DANIEL[email protected]14.05.2013300002
FLORENCE[email protected]18.06.2000450005
TOM[email protected]25.07.2001980004
HARRY[email protected]31.7.1993650006
WILIAM

[email protected]

 

30.6.1991750009

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

   
NAMEMARITAL STATUSLOCATION
MICHAELYNEWYORK
RAVIYDELHI
DANIELNMOSCOW
FLORENCENPARIS
TOMYMUMBAI
HARRYNLONDON
WILIAMNCHICAGO

 

GYANKOSH VLOOKUP EXAMPLE 4
EXCEL:VLOOKUP:EXAMPLE 4 DATA SAMPLE

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

Some new details in the form of the table has been received.
We can join the details by bringing the details from second table which is smaller one to the first table using VLOOKUP.
Let us examine the second table first.
We can see that names of the employees are given which is a unique value and would be helpful for us. (VLOOKUP ALWAYS NEED A UNIQUE IDENTIFIER otherwise it can’t be applied specially when we are joining some reports).
After applying vlookup for all the columns one by one , we should be able to achieve the target.

 

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.
GYANKOSH NEW COLUMN ADDITION FOR VLOOKUP
EXCEL:VLOOKUP:EXAMPLE 4:ADDITION OF NEW COLUMNS
  • 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.
GYANKOSH VLOOKUP EXAMPLE 4 PROCESS
EXCEL:VLOOKUP:EXAMPLE 4:RESULT
After the process we can see that we have copied both columns into the final TABLE 1 which contains all the data. Here is the final picture.
NOTE: The tables we use are small for understanding and some of you might think that this operation can be done manually also and what is the use of function.
So let me tell you guys that actually we have worked with thousands of rows and used this formula to join more than 10 different reports and its really worth it. It takes just seconds to do its job perfectly.
EXCEL:VLOOKUP:EXAMPLE 4:RESULT

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

NAMEEMAIL IDDOBSALARYEXPERIENCE
MICHAEL[email protected]12.02.19901200010
RAVI[email protected]15.03.1991100001
DANIEL[email protected]14.05.2013300002
FLORENCE[email protected]18.06.2000450005
TOM[email protected]25.07.2001980004
HARRY[email protected]31.7.1993650006
WILIAM

[email protected]

 

30.6.1991750009

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

    
NAMEMARITAL STATUSLOCATIONDOB
MICHAELYNEWYORK12.02.1990
DANIELNMOSCOW14.05.2013
FLORENCENPARIS18.06.2000
TOMYMUMBAI25.07.2001
HARRYNLONDON31.07.1993
WILIAMNCHICAGO30.06.1991
FLORENCEYDELHI20.06.1991
GYANKOSH VLOOKUP EXAMPLE 5 DATA
EXCEL:VLOOKUP:EXAMPLE 5 DATA SAMPLE

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

This problem is same as previous one except a fact that there are two employees with the same name FLORENCE.
This kind of situation comes while joining big reports when there are many repititions and it makes our final report unreliable. So for such cases we need to do something.
 
LET US CLUB TWO OR MORE COLUMNS TO CREATE A UNIQUE IDENTIFIER. E.G. WE CAN CLUB NAME AND DOB USING CONCATENATE FUNCTION. ITS HARD THAT SUCH COMBINATION WILL BE REPEATED.

 

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.

 

GYANKOSH CREATING CHECK FOR REPEATED FIELDS VLOOKUP
EXCEL:VLOOKUP:EXAMPLE 5:CREATING A UNIQUE FIELD FOR VLOOKUP
  • 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.
GYANKOSH VLOOKUP EXAMPLE 5
EXCEL:VLOOKUP:EXAMPLE 5:RESULT
After the process we can see that we have copied both columns into the final TABLE 1 which contains all the data. Here is the final picture.
After we have got our values  , we can remove the CHECK fields can be removed but before that we need to copy the complete column of the values derived and paste them as special. (SPECIAL PASTE VALUES ONLY) otherwise, the values will have an error as the values are dependent on CHECK field.
NOTE: The tables we use are small for understanding and some of you might think that this operation can be done manually also and what is the use of function.
So let me tell you guys that actually we have worked with thousands of rows and used this formula to join more than 10 different reports and its really worth it. It takes just seconds to do its job perfectly.
GYANKOSH SOLUTION EXAMPLE 5
EXCEL:VLOOKUP:EXAMPLE 5:RESULT

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

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

[email protected]

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: