Take a VLOOKUP at This!
Posted on 27th November 2016 at 11:00
Hi everybody and welcome to this week's blog.
I did a Webinar for BizSmart last Tuesday which concentrated on Excel's VLOOKUP function and other related functions.
As a consequence, I thought I would use the prep that I did and focus on VLOOKUP in my blog this week.
This function is so useful in many ways but it can be a bit tricky to get the right answer sometimes unless you are fully aware of the pitfalls ...
Please note that for reasons of space, I’m assuming that you know how to build a formula with or without the Insert Function feature.
Drop me a mail if that is a “How To” you need to know about.
What does VLOOKUP do?
Basically, VLOOKUP helps you find and return a match for a specific lookup value.
For example, you might want to know the home address of an employee matching his/her employee ID.
Another use might be that you want to find appropriate bonuses that sales people deserve based on their sales figures.
In both cases you would need to find the answers from a separate look-up table set up on a different sheet perhaps, out of the way of your working spreadsheet.
The first example relies on the system finding an EXACT match whereas the bonus example would use a sliding scale for the bonus award and so you would want the CLOSEST match to a threshold value this time.
Pleasingly, Excel's VLOOKUP function can be used in either situation that matches a value that you have in your working table. The look-up table MUST BE sorted in ascending order – a Golden Rule – see below for why.
So, when you use the function, you need to know that there are two modes for matching - EXACT mode or APPROXIMATE mode matching.
Remember, if you are looking up a Product Code, you will want to use an exact mode for matching in order to find other related information you need.
On the other hand, if you are awarding bonuses to employees based on sales, you often need to get an approximate match for their sales figure against a sliding scale in a look-up bonus table. This bonus table would probably only have a few rows in it with thresholds indicating the start of each bonus award. In this case, VLOOKUP finds the first threshold value which is greater than the target sales figure and then slides back to the threshold value that is less than or equal to the lookup value. I'll show you what I mean in the examples that follow.
Working Table and Look-up Table
How do you use VLOOKUP?
If you read the appropriate help from Microsoft on this, you will find that VLOOKUP requires you to supply four values or arguments to get the right result. Microsoft calls them Lookup_value, Table_array, Col_index_num, Range_lookup. Personally, I remember these as:
Golden Rules to get correct result every time
I recommend that you keep to the following Golden Rules when using VLOOKUP
1. Always use Absolute References for the Look-Up table
eg $B$1:$D$10 (eg if the ref says B1:D10, highlight this ref and press F4 to convert it to $B$1:$D$10)
2. Always sort the First column of the Look-Up table
in ascending order so that approximate matches work properly
3. Never omit the Match Mode argument
Be confident of getting the right result - Remember it is 0 for an EXACT match, 1 for the CLOSEST match
As I've said before, I hope you find these tips useful and you will “adopt and adapt” some of the points raised in this article in order to enhance your work in Excel.
I would love to hear your creative ideas about using VLOOKUP IN Excel.
Text me on 07973 507371 or email me email@example.com
See you next time.
“Les is more!”
PS Click here to find out about BizSmart Select - A group of specialist business owners who help others to grow and build valuable businesses.
Convert Relative refs to Absolute refs:
eg. G2 >>> $G$23 >>> G$23 >>> $G23 >>> G23
Share this post: