Microsoft Excel - Dynamic VLOOKUP() with MATCH() Function

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