A student, in my Excel Beginner to Advanced course on Udemy, recently asked an excellent question on Excel's VLOOKUP function. I thought I'd share the question and one solution here on the Office Newb blog.
Â
Standard VLOOKUP()
Â
Microsoft Excel's VLOOKUP() function is great. Perhaps one of the most sought after functions Excel has to offer. But, it has some limitations. Before we fix the limitation, lets take a peek into what VLOOKUP() looks like by default.
Â
=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)
Â
In order to perform a VLOOKUP, within an Excel worksheet, there are 4 arguments needed in order for it to work properly.
Lookup_value: Value you want to lookup
Table_array: Range where you want to lookup the value
Col_index_num: Column number in the range containing the return value
Range_lookup: Exact or approximate matchÂ
Video Tutorial on using Excel's VLOOKUP() Function
Â
Â
The VLOOKUP() PROBLEM
Â
So, you've created your VLOOKUP() functions. You, your co-workers and boss are pleased with the results. But, later a coworker has decided to change the layout of the master list, adding, removing, and/or moving columns. Now, your VLOOKUP returns an error.Â
Â
Why am I getting an Error!!!
Â
When the layout of the master list is edited this potentially breaks the COL_INDEX_NUM argument within the VLOOKUP.
Â
=VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup)
Â
Remember, the COL_INDEX_NUM argument represents the column number of the return value. After the edits made to the master list, what was once in column 3 has moved to another position., breaking the formula.
Â
MACTH() to the Rescue
Â
There are perhaps a few different ways to overcome this limitation found in Excel's VLOOKUP() function. Below is one method.
Â
Â
Download the exercise file used in the video and practice on your own:Â VlookupMatch-01
Â
Â
Â
Â
Â
Â
Â
Recent Posts
Archive
Tags