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

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).

VLOOKUP EXAMPLES PART-I is 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-5:CLICK HERE

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.

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 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.

THE ANSWER FOR THE PROBLEM IS 18.06.2000 WHICH IS CORRECT.

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.

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 the table kept in C drive from which we will find out the value

NAME	MARITAL STATUS
MICHAEL	Y
RAVI	N
DANIEL	N
FLORENCE	N
TOM	Y
HARRY	Y
WILIAM	Y

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.

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 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]

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.

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

WEEK	SALES
1	234
2	345
3	456
4	677
5	789
6	765
7	644
8	344

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

WEEK	NO. OF VISITORS
1	1234
2	2354
3	2343
4	3456
5	4677
6	4533
7	2344
8	7665

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.

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.

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

WEEK	SALES
1	234
2	345
3	456
4	677
5	789
6	765
7	644
8	344

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

WEEK	NO. OF VISITORS
4	1234
3	2354
5	2343
2	3456
1	4677
7	4533
6	2344
8	7665

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

Put the column names in the TABLE 1 at K136 as given in the following figure.

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.

TUTORIAL HOME

CONTENTS

10 VLOOKUP EXAMPLES IN EXCEL-PART II

INTRODUCTION

REVISION:
SYNTAX: VLOOKUP

EXAMPLE 1-5:CLICK HERE

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.

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

STEPS TO SOLUTION [EXAMPLE 6]

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.

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

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

STEPS TO SOLUTION [EXAMPLE 2]

EXAMPLE 8:USE OF NESTED VLOOKUP

PROBLEM:

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

SOLUTION: EXAMPLE 8

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

PLANNING THE SOLUTION

STEPS TO SOLUTION [EXAMPLE 8]

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.

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

STEPS TO SOLUTION [EXAMPLE 9]

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]

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

STEPS TO SOLUTION [EXAMPLE 10]

WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

TUTORIAL HOME

10 VLOOKUP EXAMPLES IN EXCEL-PART II

INTRODUCTION

REVISION:SYNTAX: VLOOKUP

EXAMPLE 1-5:CLICK HERE

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.

SOLUTION: EXAMPLE 6THERE 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

STEPS TO SOLUTION [EXAMPLE 6]

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.

SOLUTION: EXAMPLE 7FIND 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

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

STEPS TO SOLUTION [EXAMPLE 2]

EXAMPLE 8:USE OF NESTED VLOOKUP

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

SOLUTION: EXAMPLE 8FIND THE SUM OF TOTAL EXPERIENCE OF THREE LEAST PAID EMPLOYEES.

PLANNING THE SOLUTION

STEPS TO SOLUTION [EXAMPLE 8]

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.

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

PLANNING THE SOLUTION

STEPS TO SOLUTION [EXAMPLE 9]

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]

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

PLANNING THE SOLUTION

STEPS TO SOLUTION [EXAMPLE 10]

WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

REVISION:
SYNTAX: 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.

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.

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.

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.

PROBLEM:

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

SOLUTION: EXAMPLE 8

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

PROBLEM:

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

SOLUTION: EXAMPLE 9

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

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]

SOLUTION: EXAMPLE 10

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