INTRODUCTION
FOR THE LEARNING VLOOKUP CLICK HERE.(FOR VLOOKUP IN HINDI CLICK HERE).
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 | michael@abc.com | 12.02.1990 | 12000 | 10 |
| RAVI | ravi@abc.com | 15.03.1991 | 10000 | 1 |
| DANIEL | daniel@abc.com | 14.05.2013 | 30000 | 2 |
| FLORENCE | florence@abc.com | 18.06.2000 | 45000 | 5 |
| TOM | tom@abc.com | 25.07.2001 | 98000 | 4 |
| HARRY | harry@abc.com | 31.7.1993 | 65000 | 6 |
| WILIAM | wiliam@abc.com | 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.
OTHER WAYS TO REACH THIS ARTICLE