Ask Analytics: VLOOKUP with approximate Match option

For more than 10 years I had been using VLOOKUP function of excel extensively in my day to day job. I had designed so many Excel dashboard with blend of VLOOKUP, form control and other function such as match (), column() within. However, the child curiosity, still alive within me, was still not satiated as I used to wonder about the utility of last argument options : TRUE (Approximate Match) and FALSE (Exact Match). All these years, I used to write "0" for using FALSE (Exact Match) option.

Today (20th Oct 2015), is my Excel Christmas Day, when my long pending wish is fulfilled, as Santa has left behind a chance for me to use VLOOKUP with "1". Sharing the awesomstic feeling with you.

Please download the file and refer the same while reading the article : VLOOKUP.xlsx

A brief about the VLOOKUP function :

The syntax of VLOOKUP function :

Understanding VLOOKUP with a very basic example :

I thought of elaborating basics would be useful, while discussing about the last argument of the VLOOKUP function. I won't delve into VLOOKUP basics much now, let's directly jump to the topic I wanted to discuss.

Last argument of VLOOKUP function i.e. range lookup :

As I have discussed that the last argument provides 2 options :

1. TRUE (Approximate Match) : we write 1 for it
2. FALSE (Exact Match) : we write 0 for it

2nd option is very basic and simple, it is used for exact match; we have seen its example above too.

1st one i.e. TRUE option is used for approximate match.

How it works ?

If range_lookup is either TRUE (1) or is omitted, an exact or approximate match is returned. If an exact match is not found, the information against the next-largest value that is less than lookup_value is returned.

The pre-requisite for it to work is that first column of selected range (table_array) should be sorted in ascending order.