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

# HOW TO USE VLOOKUP APPROXIMATE MATCH IN EXCEL

## INTRODUCTION

IN THIS ARTICLE WE WOULD LEARN ABOUT THE CONDITIONS WHEN WE CAN USE APPROXIMATE MATCH OF VLOOKUP IN EXCEL.

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

THE FOURTH ARGUMENT WHICH IS DECLARED AS TRUE/FALSE OR 0/1 TELLS THE EXCEL TO PERFORM EXACT OR APPROXIMATE MATCH.

## USE OF APPROXIMATE MATCH IN VLOOKUP

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

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.

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

LEARN

• EXCEL
• JAVASCRIPT
• MORE TO COME…

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]