HOW TO USE VLOOKUP APPROXIMATE MATCH IN EXCEL

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.

NAMEEMAIL IDDOBSALARYEXPERIENCE
MICHAELmichael@abc.com12.02.19901200010
RAVIravi@abc.com15.03.1991100001
DANIELdaniel@abc.com14.05.2013300002
FLORENCEflorence@abc.com18.06.2000450005
TOMtom@abc.com25.07.2001980004
HARRYharry@abc.com31.7.1993650006
WILIAMwiliam@abc.com30.6.1991750009
EXCEL:VLOOKUP:EXAMPLE DATA SAMPLE

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.
EXCEL : VLOOKUP : EXAMPLE : RESULT

OTHER WAYS TO REACH THIS ARTICLE