Clean Up Your Lookups—Listobjects & Index/Match

Dom Yarnell - July 24, 2016

Using a formula to retrieve data from a table is pretty common in Excel. The VLOOKUP function seems to be the preferred method, but it's often difficult to ensure it's working appropriately. The first step toward improving your lookup formulas is to put your reference data in an object that Microsoft somewhat confusing named, “Table.” Obviously, any data you arrange in a tabular format could be called a table, but an Excel Table is an object that has some naming properties that are useful for retrieving data. In an effort to differentiate Tables from tabular data, I’ll refer to them by what they’re called in VBA: Listobjects.

I’ll often see Excel files with lookup formulas like this:

=VLOOKUP(100, 'Reference Tables'!$E$5:$Z$10, 12, TRUE)

The VLOOKUP function shows that the location of the reference data is on a tab called, “Reference Tables,” and we know that it’s looking at the first and twelfth columns of the range that goes from E5 to Z10. But in order to verify that this is the desired range, you have to go to that tab and check. Perhaps that formula was correct on an earlier version of the file, but someone may have since added rows to the reference data that are beyond the range in the formula. And what if someone inserted a column in the table, so that the third argument of the VLOOKUP function, 12, is no longer accurate, and you actually want the thirteenth column instead of the twelfth? When there are a lot of these lookup formulas, reviewing them can be a lot of work.

Some try to mitigate these weaknesses by naming the range being referenced:

=VLOOKUP(100, County_Lookup, 12, TRUE)

The benefit to naming an Excel range is that it’s easy to see what your formula is trying to do, which is helpful for others auditing a file for the first time. Unfortunately, the named range might not include new rows, and the insertion of columns can still undermine the VLOOKUP function. Thankfully we can solve the issue of additional rows with a Listobject.

Dom Yarnell - July 24, 2016

Using a formula to retrieve data from a table is pretty common in Excel. The VLOOKUP function seems to be the preferred method, but it's often difficult to ensure it's working appropriately. The first step toward improving your lookup formulas is to put your reference data in an object that Microsoft somewhat confusing named, “Table.” Obviously, any data you arrange in a tabular format could be called a table, but an Excel Table is an object that has some naming properties that are useful for retrieving data. In an effort to differentiate Tables from tabular data, I’ll refer to them by what they’re called in VBA: Listobjects.

I’ll often see Excel files with lookup formulas like this:

=VLOOKUP(100, 'Reference Tables'!$E$5:$Z$10, 12, TRUE)

The VLOOKUP function shows that the location of the reference data is on a tab called, “Reference Tables,” and we know that it’s looking at the first and twelfth columns of the range that goes from E5 to Z10. But in order to verify that this is the desired range, you have to go to that tab and check. Perhaps that formula was correct on an earlier version of the file, but someone may have since added rows to the reference data that are beyond the range in the formula. And what if someone inserted a column in the table, so that the third argument of the VLOOKUP function, 12, is no longer accurate, and you actually want the thirteenth column instead of the twelfth? When there are a lot of these lookup formulas, reviewing them can be a lot of work.

Some try to mitigate these weaknesses by naming the range being referenced:

=VLOOKUP(100, County_Lookup, 12, TRUE)

The benefit to naming an Excel range is that it’s easy to see what your formula is trying to do, which is helpful for others auditing a file for the first time. Unfortunately, the named range might not include new rows, and the insertion of columns can still undermine the VLOOKUP function. Thankfully we can solve the issue of additional rows with a Listobject.

**Note**: Before adding a Listobject, make sure Excel does NOT say “Compatibility Mode” in the title bar:Compatibility Mode is activated when an Excel file with an old format (one with a “.xls” extension) is opened in a version of Excel that’s later than Excel 2003. If you want to get the functionality of a Listobject in an old Excel file, just save the file in a new format, like “.xlsm,” and restart Excel before you open the file.

Highlight your table of reference data and turn it into a Listobject by typing Alt, N, T and clicking OK in the when the following window appears:

Highlight your table of reference data and turn it into a Listobject by typing Alt, N, T and clicking OK in the when the following window appears:

(Of course, you can add a Listobject by using the mouse to click the appropriate button in the Ribbon, but you should be using keyboard shortcuts to maximize your efficiency. Read my article, The First Step in Mastering Excel, to learn why.)

Then set the name of the Listobject by putting your cursor somewhere in the Listobject and typing Alt, J, T, A, and entering the new name:

Then set the name of the Listobject by putting your cursor somewhere in the Listobject and typing Alt, J, T, A, and entering the new name:

If you re-write the original VLOOKUP function, you would see it appear as the following:

=VLOOKUP(100, County_Lookup, 12, TRUE)

You might notice that this formula looks the same as the one that operated on a named range, so what was the point of adding a Listobject? Go back to your new Listobject, type in new data in the row directly beneath the Listobject, and you’ll see it automatically expands to encompass that new data. The name used in the VLOOKUP function is therefore referencing the correct data, and you don’t have to worry about whether the named data was updated correctly. But there’s still the problem of inserting columns: the third argument in the function, 12, will still be incorrect if someone inserts a column between the first and the twelfth columns. Here’s where we abandon VLOOKUP and start using the INDEX and MATCH functions.

The MATCH function returns the coordinate of a value in a range. So if the ordered letters of the Latin alphabet are in the range, A1:A26, then, =MATCH(“C”, A1:A26, 0) would return the number, 3, because "C" is the value in the third cell.

The INDEX function returns the value of a range as specified by a coordinate. So for our range of ordered letters, =INDEX(A1:A26, 3) returns “C” because the second argument in the function, 3, indicates it should return the third value.

Rewriting the lookup formula using INDEX and MATCH looks like this:

=INDEX(County_Lookup[County Name], MATCH(100, Count_Lookup[Number], 0))

The MATCH function returns the row of the “Number” column of the Listobject, and the INDEX function returns the value associated with that row in the “County Name” column. You can see this clearly without have to go back to the reference data and checking the range or counting columns. Others can insert or delete columns and the formula will still work. Also, if the name of the Listobject or column headers in the Listobject are changed, they are automatically updated in formulas that reference them. So if you change the name of the Listobject from “County_Lookup” to “County,” the formula automatically changes:

=INDEX(County[County Name], MATCH(100, County[Number], 0))

The weakness of the VLOOKUP function is increasingly recognized, as exemplified by the LinkedIn group, VLookups Anonymous, which “exists to combat the spread of rampant VLookup and HLookup use and abuse.” Adopting the INDEX/MATCH method in combination with Listobjects for your reference data will make your files easier to audit and your formulas more durable. In my next article I’ll go over how to further leverage Listobjects by referencing them in your VBA code.

=VLOOKUP(100, County_Lookup, 12, TRUE)

You might notice that this formula looks the same as the one that operated on a named range, so what was the point of adding a Listobject? Go back to your new Listobject, type in new data in the row directly beneath the Listobject, and you’ll see it automatically expands to encompass that new data. The name used in the VLOOKUP function is therefore referencing the correct data, and you don’t have to worry about whether the named data was updated correctly. But there’s still the problem of inserting columns: the third argument in the function, 12, will still be incorrect if someone inserts a column between the first and the twelfth columns. Here’s where we abandon VLOOKUP and start using the INDEX and MATCH functions.

The MATCH function returns the coordinate of a value in a range. So if the ordered letters of the Latin alphabet are in the range, A1:A26, then, =MATCH(“C”, A1:A26, 0) would return the number, 3, because "C" is the value in the third cell.

The INDEX function returns the value of a range as specified by a coordinate. So for our range of ordered letters, =INDEX(A1:A26, 3) returns “C” because the second argument in the function, 3, indicates it should return the third value.

Rewriting the lookup formula using INDEX and MATCH looks like this:

=INDEX(County_Lookup[County Name], MATCH(100, Count_Lookup[Number], 0))

The MATCH function returns the row of the “Number” column of the Listobject, and the INDEX function returns the value associated with that row in the “County Name” column. You can see this clearly without have to go back to the reference data and checking the range or counting columns. Others can insert or delete columns and the formula will still work. Also, if the name of the Listobject or column headers in the Listobject are changed, they are automatically updated in formulas that reference them. So if you change the name of the Listobject from “County_Lookup” to “County,” the formula automatically changes:

=INDEX(County[County Name], MATCH(100, County[Number], 0))

The weakness of the VLOOKUP function is increasingly recognized, as exemplified by the LinkedIn group, VLookups Anonymous, which “exists to combat the spread of rampant VLookup and HLookup use and abuse.” Adopting the INDEX/MATCH method in combination with Listobjects for your reference data will make your files easier to audit and your formulas more durable. In my next article I’ll go over how to further leverage Listobjects by referencing them in your VBA code.