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
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.
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.
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.
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 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.
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.
Examples which I am listing here are some common situations where you can use this VLOOKUP Approximate Match.
In this example, I’ll show you how to get the discount percentage from a table by creating an Approximate Match with VLOOKUP.
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.
In this example, I will show you how you can use this formula to get the grades on a marks sheet of students.
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.
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.