Two Simple Tricks To Enhance your Excel Lookups
February 26, 2014
A few weeks ago we covered INDEX MATCH as a replacement for VLOOKUP in excel. We only scratched the surface though. There are many more uses for INDEX, MATCH and VLOOKUP, using them together can even help you in a few tricky situations where a simple VLOOKUP or INDEX MATCH doesn’t do the trick.
Using MATCH to Deal with Inserted Columns in VLOOKUP
One of the major issues with VLOOKUP is breaking the formula when you insert new columns. VLOOKUP uses a static index number to reference, if the order or number of columns changes it won’t reference the correct value and you’ll have to rewrite the function.
By using MATCH we can dynamically select columns in a range, effectively selecting the column by name rather than location. It’s pretty simple to implement. Instead of using the column index number, simply insert a match formula in the place of the column index number.
VLOOKUP(lookup_value, table_array, MATCH(), range_lookup)
Incase you need a refresher; MATCH is used to find the location of a value in an array. It looks something like this,
The function then looks for a value in the specified row and returns the location of the value. So if we select the header row, we can match the placement of the value, or for our purposes, the column name. This ends up being the same thing as the column index number.
Simply modify your VLOOKUP to
VLOOKUP(lookup_value, table_array, MATCH(lookup_value, lookup_array, match_type), range_lookup)
Using INDEX MATCH MATCH to Find Values in a Matrix
We just found a way to boost VLOOKUP’s usefulness. What if you could also search in two dimensions, by both row and column, using two dynamic values?
That is exactly what INDEX MATCH MATCH does; it uses a row and column value and returns the intersection of the specified row and column.
Earlier we covered that the MATCH function returns the index of an array. So if we use MATCH in place of the row_num and col_num we should be able to get both of those values pretty easily.
If it helps, you can think of the formula as,
INDEX(The Table You’re Searching, The Row You Want, The Column Holding the Value)
Above is a table with a selection of cars in stock at a local dealer. I need to pull numbers easily without having to go through searching the entire table. For the example we’ll just have to pretend the table is much larger, making it harder to ready completely.
Using our example data, I want to know the mileage of the Civic in stock. To find this I can break it down into parts.
First we need to select the table,
Next we need to select the row. Since I’m looking for data on the Civic, I search for that value in the model column.
I can find that value by using,
Now I insert it into our INDEX function.
INDEX(B2:F7, MATCH(“Civic”B2:B7,0), col_num)
Now to get the column number, I do the same thing but I select the top level rows. To find the miles I use,
Insert it into the INDEX function and you end up with
INDEX(B2:F7, MATCH(“Civic”, B2:B7,0), MATCH(“Miles”,B2:F2,0))
Which returns the intersection of “Civic” and “Miles”, giving us a value of 50,000.
Showing off the functions flexibility, to find the F-150’s miles, I simply change the first argument of the first MATCH function to “F-150”. If I want to find the year of the Civic, I only have to change “miles” to “year” in the second MATCH function.
Highlighting the utility of INDEX MATCH, if I know the model I want and I need the ID number, which is to the right of the column, I can simply change the second MATCH function to MATCH(“ID”,A2:A7,0). Using INDEX MATCH lets me pull the ID number without restructuring the table like I would need to for VLOOKUP.
While these might not come in handy everyday, I’ve found them to be extremely useful when needed. It takes a little bit of effort to learn but when a problem presents itself you will wonder how you got by before without these.
Browse By Category
Smart Ways to Get More Marketing Budget and Better Tools
In this new live webinar, Kristin Vick from Hanapin Marketing and Jeff Sauer from Jeffalytics discuss how marketers can ensure they have the budget they need to be effective with online advertising and get the right tools to make that argument.
The Hero Conf Difference
Of the 50+ search and social conferences you can choose from, we're here to show you why Hero Conf is a can't miss event for busy digital marketers like you.
Improve Your Facebook Ads With Creative Hub and Split Testing
With the updates to Creative Hub and the Split Test feature, one can now institute a basic ad creation and testing process directly within Facebook.
A bi-weekly newsletter packed full of resources and strategies that will help make you a better PPC expert.
Hanapin Marketing | The PPC Agency of Experts Behind PPC Hero
Work Habits & Resources for the Stressed-Out PPC Manager
Feeling a bit stressed this holiday season? Get tips and resources that will help you become more efficient and alleviate stress!
A 180 of Google Analytics 360
The enterprise version of Google Analytics offers several paid search perks including, advanced data integrity, integrations and reporting.