Why INDEX MATCH is Better Than VLOOKUP

by Matthew Kuo on April 26, 2013

in Database Theory, Excel

When deciding between which vertical lookup formula to use, the majority of Excel experts agree that INDEX MATCH is a better formula than VLOOKUP.  However, many people still resort to using VLOOKUP because it’s a simpler formula.  One major driver of this problem is that most people still don’t fully understand the benefits of switching from VLOOKUP, and without such an understanding, they are unwilling to invest the time to learn the more complex formula.  The purpose of this post is to describe in detail all of the benefits of using INDEX MATCH and convince you that you should use INDEX MATCH exclusively for all of your vertical lookups.

If you don’t know how to use INDEX MATCH, please click here for a detailed tutorial.  Once you’ve learned INDEX MATCH and started using the formula consistently, you’ll reduce the number of spreadsheet errors you make, become more efficient in navigating Excel, and significantly improve your ability to create complex Excel models that have a heavy database component.

Dynamic Column Reference Leads to Fewer Errors

The key difference between INDEX MATCH and VLOOKUP is that VLOOKUP requires a static column reference while INDEX MATCH uses a dynamic column reference.  With VLOOKUP, most people will input a specific, static number to indicate which column they want to return from.  When you use INDEX MATCH, the formula allows you to manually choose which column you want to pull from.

The reason this leads to fewer errors is because when you follow the INDEX MATCH syntax, you click directly on the field containing the value you want to return.

INDEX MATCH vs VLOOKUP 01

With the VLOOKUP syntax, you specify your entire table array, AND THEN you specify a column reference to indicate which column you want to pull data from.

INDEX MATCH vs VLOOKUP 02

It’s a small difference, but this additional step undoubtedly leads to more errors.  This error is especially prevalent when you have a large table array and need to visually count the number of columns you want to move over.  When you use INDEX MATCH, no such counting is required.

The next two advantages of using INDEX MATCH also come from the fact that VLOOKUP requires a static column reference input for the values you want to return.  Please note that you could get around the static reference issue by using a formula within the VLOOKUP syntax, creating a combination formula such as VLOOKUP MATCH or VLOOKUP HLOOKUP.  However, it’s much simpler just to learn INDEX MATCH and gain all the additional benefits beyond just having dynamic column reference.

Insert Column Immunity

The greatest benefit of using INDEX MATCH over VLOOKUP is the fact that, with INDEX MATCH, you can insert columns in your table array without distorting your lookup results.  Any time you work with a large dataset, there’s a good chance you’ll need to go back to edit our columns and potentially insert a new column.  With VLOOKUP, any inserted or deleted column that displaces your return values will change the results of your formulas.

Take the VLOOKUP example below.  Here, we’ve setup the formula to pull the State value from our data table.  Because it is a VLOOKUP formula, we have referenced the 4th column.

INDEX MATCH vs VLOOKUP 03

If we insert a column in the middle of the table array, the new result is now “Seattle”; we are no longer pulling the correct value for State and must change the column reference.

INDEX MATCH vs VLOOKUP 04

INDEX MATCH has insert column immunity, so you can insert and delete columns without worrying about updating every associated lookup formula.

Easier to Drag and Copy

When working with large datasets, it’s rare to write just a single lookup formula; you’re likely going to need to drag and copy your formula to multiple cells to perform multiple lookups.  For example, let’s say that for a specific ID, I want to return a series of values from my table in the exact same order as they appear in the table.  When I try to drag and copy the standard VLOOKUP formula across (with a reference lock on both the lookup value and the table array), the lookup doesn’t work because it just pulls the same value for each entry.  This deficiency is again caused by VLOOKUP requiring a specific column reference input for your return values.

INDEX MATCH vs VLOOKUP 05

With INDEX MATCH, because you can set the return column to float (essentially by not reference locking it) the return column will move as you copy your formula over, providing you the different fields as they appear.

INDEX MATCH vs VLOOKUP 06

No Array Restriction

Another key limitation of VLOOKUP is that it requires you to specify a square table array in which your column reference cannot move beyond.  The key situation when this becomes a problem is when you append a new field to your dataset beyond your original table array.  Consider the example below:

INDEX MATCH vs VLOOKUP 07

If I append a new column to this dataset, I can’t use my original VLOOKUP formula to pull values from that new column.  If I change my column reference to “6” the formula returns an error because my table is only 5 columns wide.  To make the formula work, I’d have to update the table array I specified every time I add a new column.

INDEX MATCH vs VLOOKUP 08

With INDEX MATCH, you don’t need to specify a table array and therefore don’t face this problem.

Right to Left Lookup

One of the key benefits of using INDEX MATCH is being able to create lookup keys on the right side of your data table.  New lookup keys are generally created by running calculations on fields within your original dataset.  These keys represent your lookup column within the INDEX MATCH syntax.  When using INDEX MATCH, you can append these new lookup keys to the right side of your table and perform a right-to-left lookup to pull the values you want to return.  The INDEX MATCH syntax doesn’t care whether your lookup column is on the left or right side of your return column.

INDEX MATCH vs VLOOKUP 09

With VLOOKUP, because you can only perform a left-to-right lookup, any new lookup key you add must be on the left side of your original table array.  Therefore, every time you add a new key, you have to shift your entire dataset to the right by one column.  Not only is this annoying, but it can also interfere with existing formulas and calculations you’ve created in your spreadsheet.

Doubles as an HLOOKUP

This probably isn’t the biggest deal to most users, but it’s still worth mentioning.  A VLOOKUP restricts you to only performing vertical lookups on a table array.  I can’t, for example, lookup values across the top of my dataset and perform a horizontal lookup.  To achieve this objective, I’d need to use a completely different formula.

With INDEX MATCH, you can simply arrange both your lookup array and return array horizontally to perform an HLOOKUP.  I don’t recommend doing horizontal lookups in general, but if you ever need to, you should definitely use the INDEX MATCH formula.

Lower Processing Need

The processing benefit of using INDEX MATCH has been marginalized in recent versions of Excel because the software’s processing capabilities have expanded so significantly.  But just give everyone a brief history lesson I’ll elaborate upon this benefit.  In some of Excel’s older versions, the software had tangible processing limitations that would be noticeable if you built a huge data set.  In some situations, I was required to lookup values for thousands of rows so I could append a new column to a large table.  Once I added all of these formulas, the software would freeze up and take several minutes to calculate the return values.  I eventually had to replace my VLOOKUP formulas with INDEX MATCH to speed up the calculations.

The reason for this difference is actually fairly simple.  VLOOKUP requires more processing power from Excel because it needs to evaluate the entire table array you’ve selected.  With INDEX MATCH, Excel only has to consider the lookup column and the return column.  With fewer absolute cells to consider, Excel can process this formula much faster.

Again, please keep in mind that this benefit is probably no longer noticeable given the improvements made in Excel’s processing power.  However, if for any reason you need to run thousands of lookup formulas within a spreadsheet, make sure to use INDEX MATCH.

Conclusion

The one disadvantage of using INDEX MATCH is that it is clearly harder to learn and more difficult to remember.  Excel does not have the syntax built into the software and the syntax by itself is not intuitive.  However, this hurdle can easily be resolved by reading a simple tutorial on how to use the formula.

Making the switch from VLOOKUP to INDEX MATCH was one of the key steps I took to improve the quality of the Excel models I built.  I now use INDEX MATCH exclusively for all of my vertical lookups.  I do this not just because it’s a better formula, but also because it helps me remember the more complex syntax.  Once you’ve made the switch, and experienced the benefits of using INDEX MATCH, I promise you that you won’t go back to VLOOKUP.

{ 22 comments… read them below or add one }

Ron May 27, 2013 at 8:40 am

Inserting columns is a good thing. But it seems you cannot insert rows.
For example:
Sheet 1 has a list of 1000 users.
Sheet 2 has the same list of 1000 users with extra info.
I want to add the info from sheet 2, to the corresponding users on sheet 1.
This works fine as long as I have an exact copy of all users on both sheets.
When I insert a row on sheet 2, all data is mixed-up.
Also adding a row kills it.

Would be great to have a solution for this.

btw, thanks for your great tutorial.

Reply

Matt March 19, 2014 at 12:20 pm

It sounds like you are using static ranges. You can solve that problem by using dynamic named ranges.

Reply

Bernard June 20, 2013 at 9:00 am

Hi, am a total newbie, I just can’t figure this stuff out, who’s on first, no who’ on second…smile!

1. I am trying to create an Input Data sheet (sheet 1) that requires input in the “ReportID and First and Last Name fields”, other fields are not mandatory (fields array “A to I”, number of records, unlimited. All data should be stored on another sheet (Sheet 2)

2. Sheet 2 (Data from Input sheet 1)
ReportID FirsttName LastName IncidentDate InjuryType SpecificEvent StatusClassification DaysLeft
E12/13 409 Joseph Jones Saturday, May 04, 2013 Neck and Shoulder Cosmos Watch 35
E12/13 415 Roy Cooper Monday, June 17, 2013 Left Hand Carolina vs. NY Rangers Litigation “0”
E12/13 412 Johnny Gild Friday, April 12, 2013 Nose NC State vs. Seton Hall Information Only 84
Note, Row 32, column “I”, when “0” days left, then change cell color and email defined recipients

3. Sheet 3 – should search and display results of a search of all specific criteria in column “H”, i.e., Litigation, Information Only, Watch, Claim Paid, etc.and display the results. The criteria will be in a combo box for easy changing of search and display.

4. Sheet 3 (Displayed Results of Search of Column “H” of sheet 2)
“Litigation” (Combo box criteria – find all rows of “Litigation” and Display them on sheet 3). If I change the criteria from “litigation’s” to say “Watch”, the new result should replace the old data and display the new result.

ReportID FirsttName LastName IncidentDate InjuryType SpecificEvent StatusClassification DaysLeft
E12/13 415 Roy Cooper Monday, June 17, 2013 Left Hand Carolina vs. NY Rangers Litigation 17
E12/13 418 Ray Tucker Tuesday, June 18, 2013 ForeArm Harness Racing Litigation 72
E12/13 424 Jim Carry Monday, May 09, 2011 Sprained ankle Frazier vs. Ali Litigation 19

Many, many thanks for any assistance you all can provide. Remember, I need to know who’s on first! (Details)

Reply

kishore babu September 2, 2013 at 11:38 am

Super…. New experience for me to do miracle….

Reply

Bryan November 7, 2013 at 8:31 am

I have run into the issue you describe with the static column reference in VLOOKUPS and have recently started combining VLOOKUP with MATCH to overcome this issue. NOTE: This only works for left to right lookups; your solution for right to left lookups remains valuable.

As you mention, errors are more likely to arise when using a static number for the column reference and it also makes it more unwieldy with moving parts in a large excel sheet. However, you can use the MATCH function instead of a static column reference within the excel formula to make your formula more useful and flexible.

For example, in the example above, to lookup the city corresponding to ID 5, the formula would read: =VLOOKUP(B3,$6:$14,MATCH(C2,6:6,0),FALSE)

Using this formula, you avoid the issue of having to reformat when adding new columns, since the formula will look at all columns within rows 6-14 (you could even extend the number of rows to ALL rows, but this could lead to slower processing). Also, when cell C2 is changed to any of the data headers, the formula will react by adjusting the column reference within the VLOOKUP.

Reply

Gavin November 24, 2013 at 12:15 pm

Excellent article / tutorial and probably the best I have come across. Although I do wonder if it is possible to use in an array type scenario?

I took some time to practice the INDEX MATCH functions on my own. So just for example I do not have a unique ID per person / record but need to query anyone with a specific street name (two or more employees may live on the same street!!), the INDEX MATCH function seems to display the first instance it comes across. I am aware that basic or advanced filters or else an access database query would be the more obvious solutions; but as I am sure you know it is not always that feasible / simple.

So if I select an array of cells (for example 2) prior to entering the INDEX MATCH formula and then hold CSE to complete as an array; the same value appears in both cells, which just so happens to be the first instance it came across. For example;
=INDEX(A2:D6,MATCH(“seabury”,D2:D6,0),1)

So I was wondering if you have any thoughts or tutorials to assist in the array formula context?

Thanks very much in advance for your time and article.
Gavin

Reply

Ben December 4, 2013 at 9:15 am

I would agree the Index Match combination is much more efficient. One other disadvantage though is that tracing that is easy with V/H lookups does not work with the Index Match combination.

Reply

NITIN December 21, 2013 at 4:58 am

Dear sir,

Please mail u =match+indirect+index v\s vlookup formula file in excel
thans

NITIN

Reply

hannah January 9, 2014 at 11:32 am

Thank you for this! I’ve been toting around the past year at my job saying how vlookup is inferior to index match… I’m going to print this out as required reading for when I teach Excel training to my office.

Reply

Matthew Kuo January 10, 2014 at 3:31 pm

You’re welcome. I reference this page a lot to make my case. Glad someone else is doing the same.

Matthew

Reply

Rhett Butler January 10, 2014 at 3:26 pm

Any chance someone could email me or post the file containing this so that I may use it as a cross referencing guide for products?

Reply

Patrick January 17, 2014 at 3:36 pm

Great information, but all the examples talk about “tables”, but they are really ranges (e.g., A10:B15), not tables. I’d like to see examples using “Excel Tables” (that is, a range that is formatted as a table — using the icon “Format as Table” in the Home tab), where the table is named, columns are named, and references do not use explicit cell references. Excel Tables use structured references.

For example:
INDEX (Entities_vs_WP[#Data], MATCH (TRIM(Ents[[#This row],[Entity Name]]), Entities_vs_WP[[#Data],[Entity Name]],0),3)

Entities_vs_WP is an Excel table on one sheet.
Ents is another Excel table on a different sheet.

Bonus question (and I don’t yet know the answer): why does the above formula not work if I specify the column name reference in the INDEX formula, as in:
INDEX (Entities_vs_WP[[#Data][Entity Name]] …

The above does not work, giving a #REF! error.

Thanks!

Reply

Steven January 24, 2014 at 2:28 pm

Another advantage of this is the ability to select the row below the Match return. For example, I have a two column list. First column is pipe diameter, second column is area (in my case flow area), both listed in increasing order. By using the following formula I am able to look up the pipe diameter that has a greater flow area than needed. VLOOKUP would only be able to return the pipe diameter that is just below my required flow area. This also is a right-to-left lookup.
=Index(A1:B7,1+Match(B1:B7))

Reply

Steven January 24, 2014 at 2:33 pm

Another advantage of this is the ability to select the row below the Match return. For example, I have a two column list. First column is pipe diameter, second column is area (in my case flow area), both listed in increasing order. By using the following formula I am able to look up the pipe diameter that has a greater flow area than needed. VLOOKUP would only be able to return the pipe diameter that is just below my required flow area. This example assumes the query value, required flow area, is in A10 and returns the minimum pipe diameter needed to meet this flow area.
=Index(A1:B7,1+Match(A10,B1:B7,0))

Reply

Sam January 26, 2014 at 6:49 pm

Hi,

I am fairly new to this stuff and need to use an index match/ or vlookup to solve a problem.

The problem I have is that I have two datasets that I need to cross reference. If I use a normal vlookup then it misses some of the data. The reason is that data can be added into either of these datasets and yet not be included in the other set (therefore is not picked up by the vlookup). What I need is a vlookup that looks both ways (without creating any duplicates) if that makes sense?

Would appreciate any help on this.

Thanks!

Reply

Betty January 29, 2014 at 1:57 am

Hi,

I’m trying to figure out how to correct this #value error below on row c15 to c18. This is the formula of salesman description column: =INDEX($F$3:$F$14,MATCH(B3,IF($D$3:$D$14=A3,$E$3:$E$14),0))

a b c d e f
1 SALESMAN CASHIER
2 date amt description date amt description
3 1/2/2014 175.25 cs 1/2/2014 175.25 cs
4 1/2/2014 366.00 cs 1/2/2014 3,556.75 cs
5 1/2/2014 912.00 #N/A 1/2/2014 7,583.00 cs
6 1/2/2014 3,556.75 cs 1/2/2014 8,873.50 arc
7 1/2/2014 6,580.00 #N/A 1/2/2014 9,651.25 arc
8 1/2/2014 7,583.00 cs 1/2/2014 10,174.56 arc
9 1/2/2014 8,873.50 arc 1/2/2014 12,012.00 cs
10 1/2/2014 9,651.25 arc 1/2/2014 16,437.50 cs
11 1/2/2014 10,174.56 arc 1/2/2014 17,505.00 arc
12 1/2/2014 12,012.00 cs 1/2/2014 18,000.00 cs
13 1/2/2014 16,437.50 cs 1/2/2014 26,140.60 arc
14 1/2/2014 17,505.00 arc 1/2/2014 366.00 cs
15 1/2/2014 18,000.00 #VALUE!
16 1/2/2014 26,140.60 #VALUE!
17 1/2/2014 45,656.00 #VALUE!
18 1/2/2014 95,590.00 #VALUE!

Thanks,
Betty

Reply

Bala February 12, 2014 at 7:45 am

which web site I can see the full Vlookup & Hlookup easy details

pls tell me…

Regards & Thank You
Bala

Reply

Sarabjit Kamboj April 3, 2014 at 6:42 am

Thanks Sir, using this i have solved a major problem.
Thums up.

Reply

Krishnakumar May 27, 2014 at 1:00 am

Really nice tutorial, very useful. Thanks very much.

Reply

Rahul Pokale June 3, 2014 at 2:27 am

I use MATCH to make Column Index Number dynamic and never faced any problem. Just cant use Vlookup for Right to Left lookup.

Reply

Dean Waring June 5, 2014 at 11:44 am

Great tutorial. I’m a believer and I’ve switched. I had a problem where I had a list of golf scores and golfers in a tournament, and I needed to determine who won certain events, like skins, based on their scores. I could find the lowest score on each hole no problem, and I also figured a way to determine if a tie existed on the lowest score, thereby eliminating it from the skin determination. But, I wanted to return the winners name which was to the left of the data being searched. Index match was perfect. Next, I needed to not lookup any names where there was a tie on the lowest score, so I wrapped the Index match in an if statement to print “” where ties existed. Finally, I locked down the name column but copied the rest of the formula dynamically across the 18 holes on a score card.–thanks for the tip.

Reply

Tony Kenny November 14, 2014 at 12:43 pm

I am using this formula to return multiple results for a given match.

=IF(ISERROR(INDEX(STIGData,SMALL(IF(Vuln_ID=$Z$2,ROW(Vuln_ID)),ROW(4:4))-1,22)),””,INDEX(STIGData,SMALL(IF(Vuln_ID=$Z$2,ROW(Vuln_ID)),ROW(4:4))-1,22))

This formula does what I need but with one minor problem. I never know how many times the match will occur, so I never know how many rows down to paste the formula. Is there a was to have Excel automatically put results into a new row as long as the formula does not return a blank result?

example match on “vuln_id” range returns 5 results. This means that I have to paste the formula down 5 rows to capture everything.

Is there a way to have Excel just keep adding new rows until the results are blank? This way, I don’t necessarily have to know how many times to paste the formula.

Reply

Leave a Comment

{ 4 trackbacks }

Previous post:

Next post: