Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.

Worth $20, Absolutely Free

How To Use VLOOKUP Approximate Match Technique To Create Magic

VLOOKUP-Approximate-Match-Technique-To-Create-Magic

In this post, you will learn to create a VLOOKUP Approximate Match to get values from a range. The Idea is very simple to lookup for a value & get values which are approximately matched with lookup value. In this post, I will show how to perform this VLOOKUP Approximate Match so you will able to use this technique.

I will tell you how VLOOKUP works when you use it for the approximate match & I will also tell you about some perfect situations where you can use this amazing technique.

So let’s move ahead & grab this awesome tip.

How VLOOKUP works in this Approximate Match?

You already know that in Excel VLOOKUP Function we have four arguments. And, In below syntax, I have a lookup_value, table_array, col_index_num & range_lookup. For creating a VLOOKUP Approximate Match the whole game is about range_lookup.

Use Range Lookup To Create VLOOKUP Appropriate Match To Get Partial Matched Values

In range_lookup, you can enter two values one is TRUE & another is FALSE. When you enter FALSE, it will simply lookup for the value you have specified.

But when you use TRUE, It will perform following things.

  • In a case of numeric lookup value, If lookup value is not in the list, then it will look for the value which is smaller than lookup value. For example.

VLOOKUP Appropriate Match To Get Partial Matched Values With Numbers

When I am looking for 180 from the list where I don’t have a value equals to 180, the function has returned 174 in the result because in list 174 is the next smallest value after 180.

  • In a case of textual lookup value, if the lookup value is not in the list it will return the next approximate value from the list. In the case of textual value, you have to take some extra care. The list should be alphabetically sorted. For example.

VLOOKUP Appropriate Match To Get Partial Matched Values With Text

In above table, I am looking for value “John” and in the list, there is a value “John Geare” which is approximate match with “John” but here is a twist in the story that I am getting the value of “Jo” instead of “John Geare”.

The reason for this is, above list is sorted alphabetically and VLOOKUP returns the first value from the list which has an approximate match with lookup value. So, when VLOOKUP get “Jo” from the list it ignores all the other values which have an approximate match with lookup value.

VLOOKUP Approximate Match To Get Partial Matched Values With Approximate Text Value

Now, In above example, I have used lookup value “John Gear” but again function has returned 26 (value of “Jo”) in the result. The reason is same that VLOOKUP looks for first value in the list which has approximate match & ignores all other values from the list even they have the most approximate match with lookup value.

So the game is, You have to take care some points while creating VLOOKUP Approximate Match for text.

You don’t have to use this VLOOKUP Approximate Match every time in your work. But there are some perfect situations where you can use it to get better results. Especially when you are working with numbers. Here are some inspiring examples about VLOOKUP Approximate Match.

More Tips Related With VLOOKUP Approximate Match

When We Can Use VLOOKUP Approximate Match

Examples which I am listing here are some common situations where you can use this VLOOKUP Approximate Match.

Apply Trade Discount With VLookup

In this example, I’ll show you how to get the discount percentage from a table by creating an Approximate Match with VLOOKUP.

  • I have two tables. One of them is sales data and another one is a discount table.

Perform VLOOKUP Approximate Match To Get Partial Matched Of Trade Discount

  • In cell E1, enter below formula & drag down it to the last cell in the table.

=VLOOKUP(C2,$G$1:$H$6,2,1)

Perform VLOOKUP Approximate Match To Get Partial Matched Discount From Category Table

How does this formula work?

In above formula, I have used VLOOKUP Function with an approximate match. In the discount table, I have used minimum quantity in the category for mentioning the discount. For example, For the quantity of 58 VLOOKUP function has returned discount of 10% by looking up for next smallest value than 58 which is 45 and.

You can also use a nested IF Function for this task but VLOOKUP Function a perfect fit for this.

Apply grade in a marks sheet

In this example, I will show you how you can use this formula to get the grades on a marks sheet of students.

  • I have a mark sheet of students & a table contains grades according to marks.

Create a VLOOKUP Appropriate Match To Get Grades In Marks Sheet

  • Enter below formula in cell D1 7 and drop down to the last cell in the table.

=VLOOKUP(C2,$G$1:$H$6,2,1)

Enter Formula To Use VLOOKUP To Create A VLOOKUP Approximate Match

How does this formula work?

In above formula, I have used VLOOKUP Function with an approximate match. In grade table, I have used minimum marks for mentioning the grade. For example, For the 49 VLOOKUP function has returned discount of C grade by looking up for next smallest value than 49 which is 40.

Download Sample File

More Tips Related With VLOOKUP Approximate Match

Conclusion

I mostly use VLOOKUP Approximate Match to avoid nested IFs in my work. It not only gives me speed but it is also easy to apply. All you need a table and a VLOOKUP with Approximate Match.

Let me ask you something.

Are you using this Approximate Match with VLOOKUP in your work?

Do you think using this Approximate match is useful?

Hit Comment Box To Share Your View.

Please share this tip with your buddies on Facebook | Twitter | Google+ | LinkedIn

More Awesome Tips