Excel Use Index Match Then Match Again
The Complete Guide to Index & Match
Index & Lucifer for Flexible Lookups
Contents
The Basics of INDEX & MATCH
Excel experts mostly substitute VLOOKUP with Index and MATCH.
Here'southward why:
Dissimilar VLOOKUP, which searches but to the correct, INDEX and MATCH tin can look in both directions – left and right.
INDEX & MATCH canperform two-way lookups by both looking along the rows and forth the columns to find the intersection within a matrix.
INDEX & Friction match isless prone to errors.
Presume you accept a VLOOKUP where the terminal value you desire returned is in column N.
Your lookup value is in column A.
You lot need to highlight the unabridged A to North range and then provide your index number as 14.
If you happen to delete whatever of the in-between columns, you lot would have to update that index number.
Yous don't need to worry almost this when you use INDEX & Match.
All in all, Alphabetize and Lucifer is more flexible than VLOOKUP.
What it is
INDEX and MATCH are "nested" functions – two functions used together.
The start argument of Index is to give information technology an array. This array (range) should include your answer. You then need to specify how many rows to go down and how many columns to motility to detect the correct value. You lot cannot motility outside the INDEX range.
The syntax of INDEX is:
- The range where the return value resides.
- Number of rows to move down (the row index)
- Number of columns to move to the right (the column Index). This argument is optional. If y'all just take ane column, you can leave the column statement empty, otherwise, you lot need to specify the number of columns to move over, in the range.
Here is an case of INDEX without Lucifer:
As shown above, Alphabetize alone is not useful for identifying the number of rows or columns to move. This is where MATCH comes into play. It helps the Alphabetize office to locate the answer.
The MATCH function was designed to return the position or address of the lookup value to the INDEX function. This is what differentiates MATCH with other functions. Information technology does not return the VALUE in the cell only returns the POSITION of the cell inside a specified range.
The Lucifer function'due south syntax is as follows:
- Like VLOOKUP, Match needs a lookup (target) value. The user tin reference a cell or direct blazon the value into the formula.
- Lookup_array: The "list" (range) where the return values are located.
- Match_type: 0 for an "exact"
In the beneath example, the Lucifer role returns the value of 4. 200 is four rows downwards from the specified range.
The important point to note on the Match function is that information technology's a one-style street – you lot can only have 1 row or one cavalcade selected.
Tip: If your MATCH contains multiple criteria then you lot need to turn the multiple criteria into a one-way street. You can exercise this by wrapping the Lucifer office inside another INDEX or past converting your formula to an array – CSE – function. To watch this in activity, refer to the INDEX & MATCH Lecture in my Advanced Excel Training XelPlus.com/AdvancedExcelCourse
Putting it together
When Alphabetize and Lucifer are used together, the MATCH function finds the look up value'due south row / column index and then hands this value off to the Index function to get the lookup value. The example below shows how these work in tandem.
This formula can exist further manipulated to observe the entity with the highest sales. To do this, simply nest theMAX() function inside theMatchrole to find the location of the highest number.
The VLOOKUP function has to include all the columns in your range even if in-between columns aren't needed. Alphabetize and Lucifer only requires the render value column which tin can significantly reduce processing time in larger worksheets.
The piece of cake way to remember the syntax of the Alphabetize and Friction match is
=Alphabetize (The range location of my answer, Lucifer(The value I'm looking for, The look-up range to observe the Value, 0))
Matrix Lookup
Employ Alphabetize MATCH & a second MATCH in cases where you need to look up a value in a matrix. MATCH is used twice: once for the rows and once for the columns. In this case, the render range in the Alphabetize part involves multiple columns and the MATCH part instructs the formula to move down a sure number of rows and move over a number of columns across the sail to retrieve the desired value.
Instead of just selecting a unmarried row or column you demand to index the entire table with multiple rows and columns as your array since your value is somewhere inside this matrix.
The syntax of the Index MATCH MATCH function is:
=Alphabetize(The full range where your respond can be found in, MATCH(The value I'1000 looking for in the rows, The range I need to find the Value, 0), MATCH(The value I'g looking for in the columns, The range I need to find the Value, 0))
Building on the INDEX and MATCH Office
By nesting INDEX and MATCH in other formulas you lot tin can create more circuitous, dynamic calculations. The example below, shows how you can nest Alphabetize and MATCH in the SUMIFS part. This way you can show the SUM of either the sales column or the volume column depending on whether you select "Book" or "Sales" in G4. The summing value criteria it uses are West_01 in column A, 2014 in B and Jan in C. Once you change the value in G4 from "Book" to "Sales", the results are inverse to SUM the values from the sales column. This tin exist used in dynamic dashboard reports.
Video and Workbook
Index & Match is one of my favourite functions considering it can exist used in many complex situations.A complete understanding of this office is what separates the gurus from the but capable.
Feel free to Download the Workbook HERE.
Excel Dashboards that Inform & Impress
Use these techniques in your own reports
Unbeatable value!
23 Comments
Comments are airtight.
Source: https://www.xelplus.com/excel-index-and-match/
[…] yous're familiar with Index and Friction match you know how to solve a matrix lookup problem. You want to find a value within the matrix by […]
Very informative. Enjoyed learning the syntax of Index and Match and how when they are put together can lead to awesome lookups…….
Hi
Need Assist in this. I have a canvass for invoice and another canvass with rates for few sku's.
when i enter ii columns in sheet 1 with some text and values i desire a formula to look into the second sheet for the text entered in the 2 columns and get the value from the sixth cavalcade in the first sheet.
comment share and subscribe
Hullo Naresh – you'll get the link in one case y'all sign-up.
Leila –
Index(match)… Keen stuff! Cheers for sharing. I use it a lot, only accept found where the amount of data to search appears to interruption the functionality. Tin you ostend this?
Tku,
Jeff A
You're welcome. I haven't come across such a restriction. How many rows / columns of data?
I want to know when the length of the Index Array & Lookup array is not same then Index/Lucifer formula will properly. If not is there whatsoever method to avoid such error.
Hullo Ajit – the length of the INDEX and Friction match area has to be the aforementioned, otherwise MATCH volition render the wrong address to Index. The right formula in your case, will actually depend on the setup and what you lot're looking for.
Love your piece of work Leila, I take watched a lot of your video tutorials!
Any adventure you can do one more detailed video on index / match / get-go / sumproduct solutions looking at the subtleties of summarising data for specific date ranges (eg previous 12 months where there are v years of data in columns by month)? Especially with tables/structured references, I notice at that place are a few ways to do things but is difficult to do well…
Thank you over again!
Hi Leila, Hi Everyone,
Cheers for sharing all this swell stuff!
I have a bit of a complex lookup to do: Say I take a list of unique IDs in Col1 and the information I am looking for is either in Col. iii or Col. four depending on the item. (Col. iii & Col. four information is singled-out, no replicates). I as well have a list all all the lookup values I am interested in (some from Col. 3, some from Col. iv). Is there a mode to use index(match()) to lookup the information ?
Many thanks again!
Hello – if yous have the logic somewhere on which column to lookup the value in, you can make information technology work. Somewhere you tin can have a table with the lookup values and which column you need – you could perform a lookup on that table, get the column and apply that in your Index formula. Or y'all could utilize helper columns. Write 2 formulas – one that gets the value of Col three and the other Col 4 – so in a last cavalcade effigy out the one y'all desire to evidence based on the master table. You tin then hide the two helper columns.
Opposite to the statements at the very top of this mail as to why some and then-chosen Excel experts generally substitute VLOOKUP with Alphabetize and Match, information technology is possible (and relatively like shooting fish in a barrel) to have VLOOKUP return a value from a column to the left of the lookup column!
Assume the following:
• lookup values are in D1:D5
• corresponding return values are in A1:A5
• VLOOKUP formula is in A10
• lookup value is in D10
The following formula in A10 will return the matching value from A1:A5 for the lookup value in D10:
= VLOOKUP( D10, CHOOSE( {1,two}, $D$1:$D$v, $A$1:$A$5 ), 2, False )
As you lot can see, the Choose function with its array argument provides the magic switch. This VLOOKUP/CHOOSE formula behaves much like an Index/MATCH combo, and as well means that only the lookup column and return value column need to exist referenced no affair where in the sheet they are located. This also avoids the other problems where you lot happen to insert or delete a column in what would normally be within the range specified as the Table_array argument where yous have used a constant equally the index number (the latter is not a proficient practice in any event, as Friction match or COLUMNS should be used to make the Index number a dynamic argument.)
Comparing a naked VLOOKUP (one function) with an Index/Lucifer (ii functions) is but unfair!
This is one of my most dearest tricks when using VLOOKUP; using the Choose function to allow leftward lookup. It's dandy for rebuilding the table in memory or creating a table of simply the needed columns instead of scanning tables with dozens or hundreds of columns. Be enlightened that at some point, tables with hundreds of thousands of rows may eat more than memory than some systems tin handle. In those cases, INDEX/MATCH may be the better course of activity.
Two thumbs-upwards for inventiveness!!!
How-do-you-do Leila ….
I'k new to excel formula. I have this formula assortment :
=IFERROR(Index($AL$10:$AL$500, Minor(IF($E$2=$F$10:$F$500, ROW($F$ten:$F$500)-MIN(ROW($F$10:$F$500))+1, ""), Column(A1))),"-")
This formula extract data from AL10:AL500 base on the criteria that i enter at E2 that refer to a ready of data in F10:F500.
The problem is that the issue appeared include a indistinguishable value as well.
May i know if there is any solution if i want the formula to produce only unique outcome?
Hope my question are clear enough for u to reply.
Cheers in advance
Thank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would exist the best identify to pose your question. If you have a sample file to upload, this would greatly aid in developing a class of action.
The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I'm certain someone at that place tin inform you of the best fashion to accomplish your solution.
Microsoft Excel Tech Community
With over 25K members and near 30K posts, your solution is either set and waiting or has the possibility of existence answered more than quickly than we may be able.
Thank you for taking the fourth dimension to write. I hope you lot find success with this fantastic group of Excel enthusiasts.
XelPlus Team
is there a way of using the index and lucifer formula together in a manner that yous return non only the value in a cell, simply the entire row associated with information technology? I have a database where all the data I'm looking to alphabetize is in 1 column, but I want to render the unabridged row of each result I observe.
You will be able to perform this action once Dynamic Arrays are released. Dynamic Arrays are in their final phase of development and should be out soon.
Thanks – XelPlus Squad
I am using a circuitous Index-Match and found the upshot in column x (10 over), then I needed to add a column in the table. My result (10) is now wrong. How practice I prepare this without rewriting all of the formulas?
Thank you for your question. To assistance in answering your question, the post-obit link to the Microsoft Excel Tech Customs would be the best place to pose your question. If you have a sample file to upload, this would greatly assistance in developing a course of activeness.
The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I'one thousand certain someone there can inform you lot of the best way to attain your solution.
Microsoft Excel Tech Community
With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more rapidly than nosotros may be able.
Thank you for taking the time to write. I promise you find success with this fantastic group of Excel enthusiasts.
The XelPlus Team
Thanks to the shared diagrams and tutorials, the entire process is maximally simplified. I'll endeavour to follow your guidelines.