Hello guys, welcome back!! So, you got here looking up to practical uses of approximate match in VLOOKUP function of excel, right?
First, let me explain what is VLOOKUP and what it does? Here you go.
VLOOKUP is a very commonly used yet crucial tool of Microsoft excel. It’s been made to extract that very data you desire from that very deep pool, which’s not easy to fetch one after another.
I am going to guide you step by step how to use Approximate match in VLOOKUP function.
Function of VLOOKUP:
=Vlookup (lookup_value, table_array, index_num, [range_lookup])
If you observe, [range lookup] argument is given in square bracket!! Square bracket shows that it is an optional argument. By default, VLOOKUP takes range lookup as Exact match (0).
Problem 1: Use of VLOOKUP when we need to look value from range of data:
Let’s say, we have sales register of Hussain Ltd. from 01.04.2013 to 31.03.2017. Now we want to have financial year wise data for e.g. Period of 01.04.2013 to 31.03.14 should be defined as F.Y. 2013-14 data, 01.04.14 to 31.03.15 => F.Y. 2014-15 and so on. (Pic1)
OH! that’s so simple, just use Pivot table and group date by year wise!! But Pivot follows Calendar Year and we want to have financial year.
Then… What about if function? Yes, if function can be used here to define range, but it will be cumbersome to formulate nested if formula for each argument of financial year.
Hmm…Then…What is the solution??
Here the approximate argument of VLOOKUP can work as life saver.
Step by step guide to VLOOKUP Approximate Match argument:
- It’s easy. Just make a new insert new column on the left side of date column. (Shortcut Key = Select any cell of column of date, use “Ctrl plus Space” and then press “Ctrl plus +”.
Give a name to new column, say “F.Y.” (Pic2)
Click here to get basic and essential knowledge about VLOOKUP first.
- Start writing VLOOKUP formula, but wait…we need table!!
Approximate function is used when lookup value is to be found in range of data (exact match may not be there in table) and results are required from the same row of data range obtained by lookup value.Let’s start. Now you know VLOOKUP formula’s 4 basic argument. (Pic3)
- Make a new table having range of date and financial year against them. (Right side table of Pic 4)
Note: It is mandatory to sort the table array in ascending order for use of approximate match in range lookup.
- Now write VLOOKUP formula in cell A3 i.e. in first row of F.Y.
=VLOOKUP(b3,I3:J7,2,In the fourth argument of [range lookup] write 1 for approximate match and give an absolute reference to the table array in formula. (Pic5)
- Just Enter and copy down the same formula to cell A16. Boom!! (Pic6)
Now we have financial year wise data, filter it, sort it, do what you want!!Now you may be thinking that VLOOKUP takes only base of one cell, can we take base of two cells and look them into specific table and get answer?? Yes, you can do that by Index and Match function.Click here to learn about Advance lookup with Index and Match function – 9 Practical Examples for Beginners and Advance Users
Practice File : Practical use of Approximate Match in VLOOKUP