PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

10 VLOOKUP EXAMPLES IN EXCEL-PART II

INTRODUCTION

VLOOKUP is one of the great functions of Excel. Every Excel learner wants to get perfection over this function but one can never be perfect without enough practice.
This article describes 10 best examples to understand VLOOKUP inside out. The examples are fully solved. We have tried to make it as simple as possible to understand.

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 6:USING WILDCARD ENTRIES IN VLOOKUP

PROBLEM:

THERE IS A PERSON NAMED FLO.... BUT THERE IS SOME CONFUSION ABOUT THE REST OF THE NAME. FIND OUT THE DATE OF BIRTH OF THE PERSON.

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 H117. NAME SHOULD COME AT H117, 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
EXAMPLE DATA VLOOKUP EXAMPLES
EXCEL:VLOOKUP:EXAMPLE 6 DATA SAMPLE

SOLUTION: EXAMPLE 6

THERE IS A PERSON NAMED FLO.... BUT THERE IS SOME CONFUSION ABOUT THE REST OF THE NAME. FIND OUT THE DATE OF BIRTH OF THE PERSON.

PLANNING THE SOLUTION

This problem is not very apt for the data we are dealing with because its small for the better understanding. But when we are having large data these kind of small options can help us a lot.

In this problem, we will find out the name first and after that the date of birth.

For finding out we’ll use the wildcard character.

WILDCARD CHARACTER help us in finding some words where we have any doubt.

For a quick reference

* is for portion of word e.g. sad* would go for all word starting with sad………

? would work for a single letter e.g. sad? would go for sad ….ending with a single letter.

In this case as we don’t know the name we’ll make use of wildcard character * and nest the two vlookups.

STEPS TO SOLUTION [EXAMPLE 6]

  • Put the following formula in the cell M127
  • =VLOOKUP(VLOOKUP(“FLO*”,H118:H124,1,FALSE),H118:J124,3,FALSE)
  • The answer would appear as 18.6.2000 which is correct as the name is FLORENCE.

Let us now understand the formula.

  •  We have used two vlookups (Nested).
  • The outer vlookup starts and first argument is the name which is still to be found. For that we have applied another vlookup as VLOOKUP(“FLO*”,H118:H124,1,FALSE).
  • Inner Vlookup has the following arguments. The first argument “FLO*” would find the word matching FLO….. followed by any letters . H118:H124 is the column in which the names would be found. Column index number 1 will be returned as there is just one column and finally FALSE for exact match.
  • Inner Vlookup returns the answer as “FLORENCE”  which becomes the first argument of the outer VLOOKUP.
  • Second argument of the outer vlookup is H118:H124 which is the lookup table for the date of birth. Third column will be returned by the function and false for the exact match.
GYANKOSH VLOOKUP RESULT
EXCEL:VLOOKUP:EXAMPLE 6:ANIMATED STEPS AND RESULT

THE ANSWER FOR THE PROBLEM IS 18.06.2000 WHICH IS CORRECT.

EXCEL:VLOOKUP:EXAMPLE 6:RESULT

EXAMPLE 7:FINDING A VALUE IN DIFFERENT WORKBOOK USING VLOOKUP

PROBLEM:

FIND THE MARITAL STATUS OF FLORENCE. A TABLE CONTAINS THIS INFORMATION WHICH IS PRESENT IN A WORKBOOK NAME ABC.XLSX SHEET 3 H16:K26 LYING IN THE "SAFE" DIRECTORY IN C: DRIVE. BOTH THE TABLES ARE GIVEN.

This example we will focus on how to deal with the different workbooks. The table 1 is given below which contain different details of the employees. I just need to check one more detail and i.e. MARITAL STATUS of FLORENCE which is present in the workbook kept in C.

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

This is the table kept in C drive from which we will find out the value

NAMEMARITAL STATUS   
MICHAELY   
RAVIN   
DANIELN   
FLORENCEN   
TOMY   
HARRYY   
WILIAMY   

SOLUTION: EXAMPLE 7

FIND THE MARITAL STATUS OF FLORENCE. A TABLE CONTAINS THIS INFORMATION WHICH IS PRESENT IN A WORKBOOK NAME ABC.XLSX SHEET 3 H16:K26 LYING IN THE "SAFE" DIRECTORY IN C: DRIVE. BOTH THE TABLES ARE GIVEN.

PLANNING THE SOLUTION

The focus of this example is to just see how the formula will be made. So we should know that only the reference of the lookup table which is present in other worksheet is the main thing, rest everything is easy.
For any workbook at any other address, here is the lookup format for the workbook at different location

‘address of the workbook[filename]sheet name’!

 
 

 

STEPS TO SOLUTION [EXAMPLE 2]

For this problem the formula will be
 
=VLOOKUP(“FLORENCE”,’C:\SAFE\[ABC.XLSX]Sheet3′!H16:K16,2,FALSE)
and it’ll return our required answer.

EXAMPLE 8:USE OF NESTED VLOOKUP

PROBLEM:

FIND THE SUM OF TOTAL EXPERIENCE OF THREE LEAST PAID EMPLOYEES.

The table is given below. There are a few details given about the employees. (The table can be copied and pasted in Excel).

Kindly match the location by checking the picture if you want to follow the formulas.

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
GYANKOSH EXAMPLE 8 SAMPLE DATA
EXCEL:VLOOKUP:EXAMPLE 8 DATA SAMPLE

SOLUTION: EXAMPLE 8

FIND THE SUM OF TOTAL EXPERIENCE OF THREE LEAST PAID EMPLOYEES.

PLANNING THE SOLUTION

In this problem, we have these steps

FINDING OUT THE LEAST 3 SALARIES. For finding out the least salaries we’ll make use of SMALL function .

The syntax of the function is

=SMALL(ARRAY, kth small value)

LOOKING UP THE EXPERIENCES OF THE PERSONS WITH LEAST SALARIES.

SUMMING UP THE SALARIES.

STEPS TO SOLUTION [EXAMPLE 8]

EXCEL:VLOOKUP:EXAMPLE 8:STEP WISE STEP SOLUTION

Let us find the three least salaries using the SMALL FUNCTION.

  • Put the following formula in K162

    =SMALL(K154:K160,1)
    It finds out the smallest salary in the column
    similarly for second lowest and third lowest the formula used are
  • =SMALL(K154:K160,2)
  • and
  • =SMALL(K154:K160,3)
After this , we need to use the nested lookup and find out the corresponding experience of each salary which we extracted from the above formulas.
For the result
Put the following formula in cell K168.
=SUM(VLOOKUP(K162,K154:L160,2,FALSE),VLOOKUP(K164,K154:L160,2,FALSE),VLOOKUP(K166,K154:L160,2,FALSE))
The outermost function is SUM(NUMBER 1, NUMBER 2, NUMBER 3).
NUMERS will be looked up using the vlookup.
Individual VLOOKUPS are used to find out the experience.
Let us discuss one of them.
For the first number the formula used is
VLOOKUP(K162,K154:L160,2,FALSE)
K162 is the smallest salary which we found previously and needed to looked up to find out the experience.
K154:L160 is the lookup range which includes both the value to be looked up and value to be returned.
2 is the column index number of the experience column.
False for the exact match. Similarly the other two formulas will find out the experience and finally the SUM will sum them up.
GYANKOSH EXAMPLE 8 FINAL SOLUTION
EXCEL:VLOOKUP:EXAMPLE 8:FINAL OUTCOME

EXAMPLE 9:JOINING TWO TABLES ON DIFFERENT SHEETS USING VLOOKUP

PROBLEM:

TWO TABLES CONTAINING THE SALES DETAILS ARE PRESENT ON TWO DIFFERENT SHEETS. JOIN THE TABLES TO CREATE A FINAL REPORT.

The first TABLE 1 contains the details of sales, week wise in our TOKYO STORE. The table can be copied and pasted as per convience.

 

The details are given below.

TABLE 1 
  
DETAILS OF SALES FOR TOKYO STORE 
  
WEEKSALES
1234
2345
3456
4677
5789
6765
7644
8344

The second details which are present on a separate sheet, contains the number of visitors to the stores week wise. we have to make a final report joining these informations.

TABLE 2 
  
DETAILS OF VISITORS FOR TOKYO STORE 
  
WEEKNO. OF VISITORS
11234
22354
32343
43456
54677
64533
72344
87665
GYANKOSH VISITOR DETAILS VLOOKUP
EXCEL:VLOOKUP:EXAMPLE 9 TABLE 1
GYANKOSH VISITOR DETAILS
EXCEL:VLOOKUP:EXAMPLE 9 TABLE 2

SOLUTION: EXAMPLE 9

TWO TABLES CONTAINING THE SALES DETAILS ARE PRESENT ON TWO DIFFERENT SHEETS. JOIN THE TABLES TO CREATE A FINAL REPORT.

PLANNING THE SOLUTION

We’ll use vlookup to bring the additional column to table 1 and finalize the report.
This particular example is to examine  how to use vlookup when data is on different sheets.
 
Although we can always choose the table as shown in the animated picture and excel will take care of the naming of the cell itself but if we want to name our self the syntax is as follows.
SHEETNAME! CELL ADDRESS

 

STEPS TO SOLUTION [EXAMPLE 9]

Let us copy first column to the table 1. i.e. NO. OF VISITORS

NOTE: Although the WEEK NO. 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 K136  as given in the following figure.

 

EXCEL:VLOOKUP:EXAMPLE 9:STEPS TO JOIN THE TABLES IN MULTIPLE SHEETS
  • Put the following formula in the cell K137
  •  =VLOOKUP(I137,Sheet2!G8:H15,2,FALSE)
  • I137 is the week no. which is unique field in both the data. We will find it in the table 2 so that the value can be returned from there.
  • SHEET2  is the name of the sheet where table 2 is present. Just focus on the way it is written.
  • As we have defined it as a table, it’ll fill the values itself , if not drag the formula down and the problem is solved.
GYANKOSH EXAMLE 9 SOLUTION
EXCEL:VLOOKUP:EXAMPLE 9:RESULT

EXAMPLE 10:JOINING TWO TABLES ON DIFFERENT SHEETS USING VLOOKUP

PROBLEM:

TWO TABLES CONTAINING THE SALES DETAILS ARE PRESENT ON TWO DIFFERENT SHEETS. JOIN THE TABLES TO CREATE A FINAL REPORT.[table 2 is not sorted]

The first TABLE 1 contains the details of sales, week wise in our TOKYO STORE. The table can be copied and pasted as per convience.

 

The details are given below.

TABLE 1 
  
DETAILS OF SALES FOR TOKYO STORE 
  
WEEKSALES
1234
2345
3456
4677
5789
6765
7644
8344

The second details which are present on a separate sheet, contains the number of visitors to the stores week wise but the data is not sorted but random. we have to make a final report joining these information.

TABLE 2 
  
DETAILS OF SALES FOR TOKYO STORE 
  
WEEKNO. OF VISITORS
41234
32354
52343
23456
14677
74533
62344
87665
GYANKOSH VISITOR DETAILS VLOOKUP
EXCEL:VLOOKUP:EXAMPLE 9 TABLE 1
GYANKOSH VISITOR DETAILS
EXCEL:VLOOKUP:EXAMPLE 9 TABLE 2

SOLUTION: EXAMPLE 10

TWO TABLES CONTAINING THE SALES DETAILS ARE PRESENT ON TWO DIFFERENT SHEETS. JOIN THE TABLES TO CREATE A FINAL REPORT.

PLANNING THE SOLUTION

We’ll use vlookup to bring the additional column to table 1 and finalize the report.
This particular example is to examine  how to use vlookup when data is on different sheets .
This example is exactly same as EXAMPLE 9 except the fact that TABLE 2 has the data not sorted properly.
AND IF YOU CAN REFER, WE DIDN’T USE THE $ SIGN TO FIX THE TABLE.
 
Although we can always choose the table as shown in the animated picture and excel will take care of the naming of the cell itself but if we want to name our self the syntax is as follows.
SHEETNAME! CELL ADDRESS

 

STEPS TO SOLUTION [EXAMPLE 10]

Let us copy first column to the table 1. i.e. NO. OF VISITORS

NOTE: Although the WEEK NO. 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 K136  as given in the following figure.

 

EXCEL:VLOOKUP:EXAMPLE 10:STEPS TO JOIN THE TABLES IN MULTIPLE SHEETS
  • Put the following formula in the cell K137
  •  =VLOOKUP(I137,Sheet2!G8:H15,2,FALSE)
  • If you noticed in the animated picture that #N/A error appeared in many places. It happened because when the formula was dragged or auto filled, the relative position of lookup table changed, and as because this time the data was random the formula failed.
  • So for this, we need to use absolute reference and apply $ sign in the address of lookup table.So formula becomes
  • =VLOOKUP(I137,Sheet2!$G$8:$H$15,2,FALSE)
  • I137 is the week no. which is unique field in both the data. We will find it in the table 2 so that the value can be returned from there.
  • SHEET2  is the name of the sheet where table 2 is present. Just focus on the way it is written.
  • As we have defined it as a table, it’ll fill the values itself , if not drag the formula down and the problem is solved.
GYANKOSH SOLUTION EXAMPLE 10
EXCEL:VLOOKUP:EXAMPLE 10: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]