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

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

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

#### STEPS TO SOLUTION [EXAMPLE 2]

**=VLOOKUP(“FLORENCE”,’C:\SAFE\[ABC.XLSX]Sheet3′!H16:K16,2,FALSE)**

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

**=SUM(VLOOKUP(K162,K154:L160,2,FALSE),VLOOKUP(K164,K154:L160,2,FALSE),VLOOKUP(K166,K154:L160,2,FALSE))**

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

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

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

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

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.