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

Contents

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
MICHAEL[email protected]12.02.19901200010
RAVI[email protected]15.03.1991100001
DANIEL[email protected]14.05.2013300002
FLORENCE[email protected]18.06.2000450005
TOM[email protected]25.07.2001980004
HARRY[email protected]31.7.1993650006
WILIAM[email protected]30.6.1991750009

 

VLOOKUP EXAMPLES SAMPLE TABLE
EXCEL:VLOOKUP:EXAMPLE 3 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.
GYANKOSH VLOOKUP EXAMPLE 3
EXCEL:VLOOKUP:EXAMPLE 3:RESULT

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

LEARN

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]

Like this:

Like Loading...

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

*CLICK ANYWHERE ELSE TO CLOSE THIS WINDOW
%d bloggers like this: