**VLOOKUP example 1: Approximate Match**

If you have text in the lookup column, most of the time approximate match does the job without any problem. And you can even skip the optional argument altogether as Excel will default to approximate match. Here is one example:

**VLOOKUP Example 2: Problems with Approximate Match**

However, this gets serious and completely unreliable **pretty fast** if you have numbers in the lookup column.

Lets understand it with an example. We have the same data as we used through this guide with one additional column of serial number.

Go to Sheet 2 of practice workbook and in J1 put 2 as we want to return the name of the student with this roll number. Now in cell J2 put the following formula:

=VLOOKUP(J1,A1:F23,2,TRUE)

The name you get is AYAAN which is wrong. Correct name is IMRAN.

Because we are using approximate match, Excel starts from the top and stops right where it finds the nearest match and gets the result from row above it. As our lookup value was “2” it started from top and moment it reached 3 and assumed 2 is absent so returned the result available against serial number “1” which is AYAAN.

To avoid this problem we either need to **sort **the data or use **Exact **match type in the VLOOKUP function.

**VLOOKUP Example 3: Fixing Approximate Match with Sort**

With sorted serial number column results are as following:

**VLOOKUP Example 4: Exact Match**

However, sorting the data is not always the option therefore, exact match is a better solution as it works even on unsorted lookup column.

Repeating the same with Exact match however corrects the mistake as Excel will consider the whole column first and then return the result without stopping in between. The formula will be:

=VLOOKUP(J1,A1:F23,2,FALSE)

**VLOOKUP example 5: Proof – Approximate Match isn’t bad!**

So you must be wondering if approximate match is this bad and see no good use of it then why is it even there. Well there are situations where approximate match works extremely good for us saving tons of effort and manual tinkering.

Consider following example where we have grades to assign to students against marks obtained. Grades are based on particular brackets.

A perfect situation where VLOOKUP’s approximate match can help us!

Have your mouse over column I header and right click > insert. This will insert a new column. Make a new heading called Marks and fill the cells as following:

Now go to cell F2 and write the VLOOKUP function with approximate match:

=VLOOKUP(E2,$I$1:$J$7,2,TRUE)

Once the formula is inserted in cell F2 you can double click the fill handle to populate the formula downwards for all the students. Following animation shows all the steps with essential keystrokes