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.
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.
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.
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 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.
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.
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:
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.
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.
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.
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.
It sounds like you are using static ranges. You can solve that problem by using dynamic named ranges.
Hi Matt,
I would like to insert rows also.
If using dynamic named range, it is created with the INDEX function, using the COUNTA function? eg : $A$2:INDEX($A:$A,COUNTA($A:$A))
So i need to combine them into index match function? If yes how to apply on them?Or they both are stand alone function?
I have been searching around for whole day and could not find any dynamic name range that can apply in index match.
Appreciate if you can guide me how to do.Thanks!
Regards
Ashley
Hi Ashley,
I would use something like this (notice that I mark whole columns):
=INDEX(Sheet2!B:B,MATCH(A2,Sheet2!A:A,0))
…where…
– column B in Sheet 2 is where the additional info is located
– A2 is the specific user in Sheet 1 (can later be dragged down to A3, A4, etc)
– column A in Sheet 2 is where the user list is (together with additional info)
Hope it helps.
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)
Super…. New experience for me to do miracle….
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.
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
I would imagine you’ve solved this problem by now, or moved on. If you have a situation where you can use a Pivot table, that’s your simplest solution. Add the pivot table and filter by street name, drag in whatever fields you want.
Assuming you don’t want to or can’t use a pivot table, I’d probably add a new table field with a name like “STREETCOUNT” and then set the value as something like –
=[@[street field]&”-“&COUNTIFS($D$2:$D2,$D2)
Your first “seabury” will show as “seabury-1” the second as “seabury-2” and so on.
I’m not sure how you plan to select the street name (validated list maybe?) but you can set your results table to show only a number of rows equal to how many instances of seabury there are with a counter column using something like –
=IF(COUNTIFS(table[street field],Filter_value)<prior_counter+1,prior_counter+1,"")
Then your lookups first check the counter column – if its blank, they should be too. Otherwise you can use your index/match formulas to pull data with your row check being:
Filter_value&"-"&Counter_value
You don't have to use the dash, but I've found it helps me not get confused.
One final note – if your users aren't that great about making sure spacing and stuff is the same, I'd run all the street names through a TRIM just to get rid of any spacing issues.
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.
Dear sir,
Please mail u =match+indirect+index v\s vlookup formula file in excel
thans
NITIN
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.
You’re welcome. I reference this page a lot to make my case. Glad someone else is doing the same.
Matthew
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?
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!
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))
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))
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!
Possibly, index,match,match?
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
Change $F$14, $D$14 and $E$14 to $F$18, $D$18 and $E$18 or to $F18, $D18 and $E18. Copy down.
which web site I can see the full Vlookup & Hlookup easy details
pls tell me…
Regards & Thank You
Bala
Thanks Sir, using this i have solved a major problem.
Thums up.
Really nice tutorial, very useful. Thanks very much.
I use MATCH to make Column Index Number dynamic and never faced any problem. Just cant use Vlookup for Right to Left lookup.
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.
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.
Thanks for the helpful info and advice in this tutorial!
One note – in your discussion of the advantages of INDEX MATCH over VLOOKUP, you didn’t mention another very important point (the one that brought me here): with VLOOKUP the lookup column must be in ascending order, which in my case was a very cumbersome problem. With INDEX MATCH, not so!
I am using this formula to return multiple results for a given match.
=VLOOKUP($B4,INDIRECT(“‘”&$B$3&”‘!B3:X100”),MATCH(F$3,LH!$B$3:$BX$3,0),FALSE)
But I would like to change the “LH!” for user input at Colume B and Row 3, how do i write it into my excel function?
I am trying below but not working
=VLOOKUP($B4,INDIRECT(“‘”&$B$3&”‘!B3:X100″),MATCH(F$3,”‘”&$B$3&”‘!$B$3:$BX$3”,0),FALSE)
Anyone can help? Thanks in advance!
Embed the indirect function again like you did in the vertical lookup section. The syntax is the same, just make sure your lookup range is just the top row, not the entire table.
Mr. Kuo
I am fairly new to Excel but found the need to build spreadsheets and write formulas. I have written VLOOKUP formulas (simple ones) but cant seem to advance to a more difficult one which i need. I will use a phone book page for an example.I need my lookup value to search the page for a name and return the adjacent number. I have been trying to use INDEXMATCH to do this with no success. I print the spreadsheets periodically and dont need to print 6 sheets with 2 columns vs. 1 sheet with 10 columns. Your website is very helpful in increasing my knowledge on formulas. Thank you in Advance Linda
Hello, one difference I’ve found that is limiting my use of INDEX MATCH over VLOOKUP is the ability to sort the data that contains the lookup formula. I setup a table where most of the data across the rows is being pulled from a different sheet, based on a cell in its same row.
Example of table:
ID Colour Part#
1 Lookup formula based on ID (1) Lookup formula based on ID (1)
2 Lookup formula based on ID (2) Lookup formula based on ID (2)
If I use INDEX MATCH, when I sort the data, the cell reference that I’m using for the lookup value (ID) doesn’t move with the data sort. After the sort, the formula is still referencing the original lookup value cell location (incorrect row) . When I sort data that use VLOOKUP, the lookup values moves with the sort.
Is this a limitation of INDEX MATCH?
To make Vlookup column lookup dynamic you can use the Column() function. Column(D) for example would return a column look up of 4 and when you copy the formula over it’ll change to Column(E) and so on. If your Vlookup doesn’t start in column A you have to subtract the lookup column from from one column before where your lookup range starts.
=VLOOKUP($A2,$D:$G,COLUMN(F1)-COLUMN($E1),0)
This will look up the value in A2 within D:G and return the value in F. COLUMN(F1)-COLUMN($E1) = 6-5 = 1. If you drag this over to the right one it’ll update to:
=VLOOKUP($A2,$D:$G,COLUMN(G1)-COLUMN($E1),0) which gives the result in G.
Great information, I’ve been using index match for a couple of years and recently discovered a bug in excel 07 (if anyone uses that anymore).
It is specific to looking up values from another sheet. The formula does not work if you reference the sheet you are in. For example if you are in Sheet 1, the formula cannot at any point have ‘Sheet1’! in it. This gives errors when manipulating the data.
Why would you redundantly have Sheet1 in the formula if you are already on Sheet 1, you ask? This can happen when switching back and forth between sheets while in the midst of writing a formula. There are other forums highlighting this issue, I just wanted to bring it up here and maybe save someone a lot of headache.
All said and done, I still use index match everyday.
I am having an issue, do you know of limitation to the amount of rows you can use the match and index figure. I can not get the match function to work over 295 row. I not sure why it just displays #N\A when i look at some past row 295. Please help.
Sir,
I want knowledge about vlooup formula regarding matching the table and find some thing a table. sir pls. you explane me with exmple. ex i want search some address in a table. how can do it.
I need to look for numbers from Column A to Column B.
* Column A has 96 Rows and Column B has 36 Rows, both filled with phone numbers.
I need to find the 60 missing phone numbers from Column B to Column A. VLookup and Index-Match doesn’t seem to solve my problem. Help?
Misstee,
If you haven’t already found a solution to your problem, below is one way to do it. This is a really simple way, and there might be other better ways to do it.
Column A has complete list.
Column B (rows 2 to 6 in my example) has partial list.
Column C =MATCH(A2,$B$2:$B$6,0)
Column D =IF(ISNA(C2),A2,””)
You can combine the formulas in columns C and D into one.
Hello Mark
what will happen if there is more than one record having the identical lookup value?
for example searching for a name like “Brown” – will it find all the browns and how can one handle that?
regards
fred
If you have more than one record with an identical value, Excel will just use the one that appears first, based on how you sorted the data. It will then return a value that corresponds to that first appearance and ignore the subsequent ones. I’d recommend for you, either getting rid of your duplicates in the first place, or changing your lookup value column so that it is a unique key (Brown1, Brown2, etc.) Below is a link to an article for removing duplicates.
http://www.mbaexcel.com/excel/how-to-remove-duplicate-values-in-excel/
Does anyone know where I can find a blank “VS 1 Form” to fill out?
An awesome article!
Thanks a ton for sharing this knowledge.
No problem, Sunil. Thanks for reading.
I have a holiday calendar for several years in one table. Can anyone help – How to arrange this data by week and show holiday against week? I want to reference this data in other worksheets and hence arranging this way will help me to use formulae on other sheets. I want the data to be: col A having week numbers and column B showing holiday for year 1, col. C showing holiday for year 2, etc.
Fiscal Week
2015 2014 2013 2012
Valentine’s Day 2 2 2 3
President’s Day 3 3 3 4
Ash Wednesday 3 5 2 4
St. Patrick’s Day 7 7 7 7
Easter 10 12 9 11
Mother’s Day 15 15 15 16
Memorial Day 17 17 17 18
Flag Day 20 19 19 20
Father’s Day 21 20 20 21
Independence Day 22 22 22 23
Labor Day 32 31 31 32
Veterans Day 41 41 41 42
Thanksgiving 43 43 43 43
Christmas 47 47 47 48
New Year’s Day 48 48 48 49
ML King Day 51 51 51 52
This is very helpful and easily understandable tips. Thanks
VLOOKUP CHOOSE is something to look into
Thanks, this explanation is really helpful. I have had trouble understanding other tips but yours really make sense to me.
I really liked this article, I wanted to get away from setting my data to Ascending, just leaving the raw as is, because I also needed a column to concatenate two fields, in the raw data, so I could concatenate two fields in the look up.
My problem, is based on a field in the spreadsheet (with data validation to a specified list of months), I want to either change the array or sheet the Lookup or Link-Match is grabbing from, I could add a date and concatenate a third field, but then I will have a different page of look-ups to either modify or deal with two steps monthly.(Current and Cumulative Reports)
When I try to use a specific cell that would either contain the name of the array or the name of the sheet, excel uses only that cell and I get an error.
Any thoughts?
Sarah,
I know this is a few months late, but i was just reading your question. This is a problem i have dealt with in the past and hope you can reap the benefits of if you still need it. This can be solved with the addition of an indirect to your lookup array. For example:
INDEX(INDIRECT(“‘”&$A$1&”‘!A:L”),MATCH($B1,INDIRECT(“‘”&$A$1&”‘!E:E”),0),MATCH(“Information”,INDIRECT(“‘”&$A$1&”‘!7:7”),0))
where A1 has the month you are referencing. This also assumes that the data is found in columns A through L on each month’s tab and your lookup value is in the “B” column.
Index Match Match can be confusing at first, but it is well worth learning if you want lookups to be really powerful. When combined with putting data in Tables, things get far easier also.
Hi,
Sorry if my question is silly. But still I am asking since I don’t know the answer.
My Vlookup Value has 2 fields merged, for example my Vlook up formula looks like below. Hope you will understand my question if you see the formula.
VLOOKUP($C40&AI$2,Raw!$A:$S,6,FALSE)
How you will do this with Index Match formula..?
Thanks in advance for the reply.
Hi Prasad,
Yes, merged lookup values are pretty common, and you should be able to perform the same lookup action by using INDEX MATCH instead. Please see the link below with instructions and the specific syntax required at the bottom of the post:
http://www.randomwok.com/excel/how-to-use-index-match/
Let me know if that helps.
Best,
Matthew
Hi and thanks for the great article.
One question I have about the limitations I have found with both INDEX MATCH and VLOOKUP is the problem with duplicate values.
I learned the hard way that VLOOKUP when evaluating the array will select the FIRST match that it comes to and stop. If the same search term is found subsequent to the first term on a following row it selects the first value and not the second value. I thought INDEX MATCH would fix this. I thought INDEX MATCH worked whereby INDEX returns the value in a cell with x,y coordinates of the cell it finds, and we use the MATCH function to define the y coordinate. So I thought that when Excel finds an x value, even if it is a duplicate of a previous x value, the MATCH function would ensure the proper return value would be selected. What I found instead is that INDEX MATCH is returning the same result as VLOOKUP did and is not looking beyond the first match it finds.
Maybe I don’t understand how to use INDEX MATCH properly so any advice would be greatly appreciated. Great article for sure though so thanks very much.
Tom
Hi Tom,
Your thinking is correct in that, just switching from VLOOKUP to INDEX MATCH will not solve this issue. Basically, what it comes down to is, Excel needs a way to differentiate duplicate values if you have it in your data. Those two lookup functions by themselves do not provide a measure of differentiation.
There are a couple ways you could address this (such as just removing the duplicates or making the values themselves distinct). One unique method is the anchored COUNTIF:
http://www.mbaexcel.com/excel/how-to-use-the-anchored-countif-formula-in-excel/
Let me know if this helps.
Best,
Matthew
One alternative available when looking at numeric values is to us SUMIFS across a sufficient number of fields to assure uniqueness. (e.g. Customer A on OrderDate Y, or Claim X and ClaimLine Z)
This will not work for text values and is not as elegant as adding a proper unique index column, but will suffice if your source data is in a file that you cannot modify for whatever reason.
Use an INDEX SMALL IF array formula.
First Value
{=INDEX($B$7:$B$12,SMALL(IF($A$3=$A$7:$A$12,ROW($A$7:$A$12)-ROW($A$7)+1),1))}
Second Value
{=INDEX($B$7:$B$12,SMALL(IF($A$3=$A$7:$A$12,ROW($A$7:$A$12)-ROW($A$7)+1),2))}
Hi, this is a good article.
I have a question. What if the index column in VLOOKUP is not static but dynamic? For example,
VLOOKUP(lookup_value,table,match(my_string,string_table,0))
Can this be expressed using INDEX and MATCH?
Hi!
I’ve been using VLookup and tried to use Index Match instead. I’m finding limitations with index match especially when I filter. How can I fix this problem?
Example:
Raw Tab – trying to lookup the date for a Process Order number
COOISPI Tab – process order and date provided
My formula I have been using in Raw Tab is as follows: =index(cooispi column with date,Match(raw column with PO # I’m looking for date,cooispi row with matching PO number,0))
Here are my problems – not all of my cells in Raw tab have a date so I have to filter for my NA’s and just pick their posting date. I need INDEX MATCH to show me the dates but once I ascend the dates in order INDEX MATCH gives me wrong dates for PO’s or gives me NA’s for PO’s that have dates in the COOISPI tab. I’ve also noticed I have to put the COOISPI data rows as “Text to Columns” or it won’t work. It’s hassle when I repaste every day into the COOISPI tab. I’ve tried picking the cell only but I get NA’s once I ascend in order of dates, then I tried picking the entire Raw row but then I get incorrect dates.
Seems like filtering and sorting the data is messing INDEX MATCH up. I’ve resorted back to Vlookup because I’m not having that issue. I can sort, filter etc without any incorrect data or NA’s.
Hi,
could someone me explain why?
When i have, HUNDREDS of THOUSANDS cells using INDEX/MATCH freezing my computer totally, and after i change one cell the old results do not change.
With Vlookup with same numbers of cells and static ranges (which i need change each time i import data, with replace) Excel seems work faster.
I really dont see why this functions looks faster for you.
.
I spent few hours changing all vlookup formulas to INDEX/MATCH and then i had to decide back to normal static ranges and VLOOKUP.
One of the additional benefits to using Index match is that you can use it for multiple lookup criteria (a one-way, two-way, or more) matching. For example your index stays one column but your match can use the & option to match as many criteria as you want. You just have to be sure to turn your formula in to an array for it to function by placing the cursor at the end of your formula and pressing Ctrl-Shift-Enter or equivalent on a Mac. This is what addes the {} to the beginning and end of your formula. For example here is a formula I used today.
{=INDEX(‘lookuptable’!B$1:B$73,MATCH(D2&E2&F2,’lookuptable’!C$1:C$73&’lookuptable’!D$1:D$73&’lookuptable’!A$1:A$73,0))}
Index Match is incredibly powerful and indispensable to an advanced Excel user. I can’t believe it’s not more frequently used.
Using Vlookup and Choose solves most of the listed problems (including right to left lookup).
=VLOOKUP(B3,CHOOSE({1,2},B7:B14,E7:E14),2,0)
NAME DURATION HOL_SZ HOL_SEC HOLE_ST_DPTH HOLE_END_DPTH
this is a sample column header from an extremely large and always changing spreadsheet I have. I am trying to make a code that will recognize the name I input and fill in the values for the rest of the column headers when the name matches. I want to use that data to create multiple curves, graphs etc. This is a huge spreadsheet. Can someone assist with how to do this?
This article is so true! I replace a bunch of DGET formulas in a vary large Excel file with INDEX/MATCH, and it sped up the calculations probably 20-fold! It went from taking like 20 min to calculate to about 45 seconds. I think I have used VLOOKUP once since learning INDEX/MATCH, and even then I had to change that VLOOKUP to INDEX/MATCH because it wasn’t working as I wanted it do. I also used INDEX/MATCH to pull data out of a large table in which the users frequently change the order of the columns. Now it doesn’t matter how the columns are changed.
i have another solutionfor the INDEX-MATCH formula in the Easier to Drag and Copy section. You can insert two MATCH formulas in the INDEX formula. The first MATCH identifies the Row and the second MATCH identifiles the column:
=INDEX($C$7:$E$14,MATCH($B3,$B$7:$B$14,0),MATCH(C$2,$C$6:$E$6,0))
You enter this formula in the cell C3 and copy it over to cells D3 and E3.
why does index match return errors when using tables
Quick update: If you Name cell B3 “Lookup” and change C3 to =INDEX(City,MATCH(Lookup,Zip,0)) You can move copy B3 to any cell and also C3 as well and everything still works.