Yes, I know it's an array formula. The formula in cell C14 returns multiple values from column Item. Yes, it is possible. Excel Match Multiple Criteria with Formula . The INDEX and MATCH combo is potent and flexible, and you'll see it used in all kinds of formulas, from basic to very advanced.However, while VLOOKUP allows you to perform lookups with a single function, INDEX and MATCH requires two functions, one nested inside another. How to use the SUMIF Function in Excel . In the previous post, we used one excel formula to get the position of the nth occurrence of a value based on one criteria, at this moment, we can also use it in the new formula to extract the nth match value using INDEX function. Here's the formula you will need to use: =ArrayFormula(INDEX(B2:B24, MATCH(CONCATENATE(F2:F4), A2:A24&C2:C24&D2:D24, 0),)) Don't panic! = Popular Articles: 50 Excel Shortcuts to Increase Your Productivity. In the following article I will show you how you can use an Index and match formula with multiple criteria to return text to a cell. Upload picture to postimage.org or imgur Can someone please help me figure out a formula to index a specific batch# from an array which is based on matching an amount, a sponsor code, and a date that may be greater than or equal to the specified date? To evaluate multiple criteria, we use the multiplication operation that works as the AND operator in array formulas.Below, you will find a real-life example and the detailed explanation of … INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ). When I use the evaluate formula tool, it matches both the date and symbol as a string (ie. The SMALL function returns the k-th smallest value, depending on what number ROWS($A$1:A1) returns. ©  Copyright  2017 - 2020 Excel How  All Rights Reserved. The first two criteria are a date and a ticker symbol. So, you're an INDEX MATCH expert, using it to replace VLOOKUP entirely. When you multiply the values, If either value is FALSE (0), the result is zero; If both values are TRUE (1), the result is 1; Only the 8th row in our list of items has a 1, because both values are TRUE in that row. Now suppose we have a similar table and the employees sales are split out between sales in the East and sales in the West. If you enter the formula in cell F2 the formula becomes: =INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category)*(COUNTIF($F$1:F1,Category)=0), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1))), can u do it if you have multiple person in same organization like. So far I've built a formula from the website to fill six criteria, and I've hit a snag. Now let’s say we need to lookup Guy’s sales for the West. This handy Excel non array formula is good when you want to match a number of criteria to return a text value. The following link takes you to an article that demonstrates how to extract records based on a lookup value: Lookup With Multiple Criteria. SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1)). Hello, I have this formula below where I am trying to build a data validation list based off of a set of data within a file. Insert your formula here. : The lookup_array values must be placed in ascending order (e.g., -2, -1, 0, 1, 2; or A-Z;, or FALSE, TRUE. 2. I want to use the Index Match function to look up the production rate based on the day of the month. No matter whether you are using Excel or Google Sheets, you can use the formula same way. Also, we also talked that how to Lookup the Value with Multiple Criteria to find the first occurrence match in excel.. The need to look up and match multiple criteria is quite common; however as with most things in Excel there are many ways to ‘skin a cat’, I'll share two options with you here. Here, is an example. The need to look up and match multiple criteria is quite common; however as with most things in Excel there are many ways to ‘skin a cat’, I'll share two options with you here. https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#multiple. The formula in cell C1 below returns FALSE because the value in cell A1 is equal to the value in cell B1. Comments. How to use INDEX and MATCH with multiple criteria. Which Match Type to Use; Match Type: What It Does: Rule: Example: 1: Finds the largest value that's less than or equal to lookup_value. Without concatenating values in a helper column, or in the formula itself, there's no way to supply more than one criteria. TOP. We must not get caught up with how many conditions need to be satisfied. The formula in cell C14 returns multiple values from column Item. Thanks for sharing, Matt. But there are still a few lookups that you're not sure how to perform. I need <>"Gross"; <>"Specialty"; <>"Fixed; <>"% in lieu". Closest Match. Table 1 contains the criteria of the lookup (Year and Number), as well as the value I want the formula to return (Score). Is there a way to do that all in one formula? Lookup to the Left. See why Index Match is better than VLookup. The SMALL function returns the second smallest number in C15. Excel Match Multiple Criteria (Table of Contents) Introduction to Match Multiple Criteria in Excel; How to Match Multiple Criteria in Excel? The MATCH function returns the relative position of a value in an array or cell reference, this example has two values that makes it return an array of 2 values. Formulas are the key to getting things done in Excel. Do you want to look up a value based on multiple criteria? And then VLOOKUP-CHOOSE or INDEX-MATCH function to trace down the names. Copy or enter the below formula into a blank cell where you want to locate the result: The formula works as long as I don't change the D1 which essentially makes it useless to me. The SMALL function returns the position of the second occurrence of the string text “jenny” in the range B2:B10. Let’s wrap the things up with some points to be remembered. I have created the equations, but the only problem is the lookup returns the closest test date. The third criteria is a text string written as "Buy". In Excel, TRUE is equal to 1, and FALSE is equal to zero. The function gets the largest value less than or equal to your search_key. The IF function cant process error values so to solve that I … Enter the criteria. We have 2. But the problem here is that it has clashing scores. Convert array values to boolean values. Feel free to comment and ask Excel questions. If you input INDEX MATCH without the “0” in the MATCH formula, to indicate that you want an exact match, Excel will assume that you want to input a “1” instead of a “0”, and will return a result for you. The formula is creating duplicates. I want to avoid creating a list of results with duplicate values, then building a separate formula to create a list of unique values. How to use the COUNTIF function in Excel. Match function will return the row where match found and Index will return the corresponding value. With the value “1” in the MATCH syntax, you’re telling Excel that you want Excel to find the largest value less than or equal to your lookup value. Next, let’s analyze the INDEX function. |     Privacy Policy   |   Term Of Service   |   RSS, get the position of nth occurrence of a value in column, Find nth Occurrence with Multiple Criteria Using INDEX/MATCH, Transpose Values Based on the Multiple Lookup Criteria, How to find nth Occurrence with Multiple Criteria Using INDEX/MATCH, How to Count Row That Contain Specific Value in Excel, How to Sum if Cell Contains Text in Another Column, How to Count Occurrences in Entire Workbook in Excel, How to Count Numbers Nth Digit Equals to Specific Number in Excel, How to Count Numbers that begin with Specific Value  in Excel, How to Count Number by Range with COUNTIFS Function in Excel, How to Count Matches between Two Columns in Excel, How to Sum Data if Between Two Numbers in Excel, How to Sum Data if Begins with/End with/Contains in Excel. So it returns $165. Is there an easy way I am not seeing? Everything is based on lookup values, which means that even if rows or columns are inserted into the table, the formula will still find the required data. Make sure you subscribe to my newsletter so you don't miss new blog articles. How to use Excel INDEX MATCH (the right way) Select cell G5 and begin by creating an INDEX function. =INDEX(A2:B6,3,2) Will return “Alex”. I need to add more criteria to exclude. < becomes < and > becomes > How to add VBA code to your comment Hi, In this scenario, I need a formula to return a specific value based on (1) a criteria with an exact match, and (2) a range criteria where there is not always an exact match. ROWS($A$1:A1) has two cell references, the first one is an absolute reference and the second one is a relative cell reference. It takes a range and returns the value under given row and column numbers. The final trick that INDEX(MATCH()) allows you to do is to interrogate an array based on multiple lookup criteria. How to use the INDEX and MATCH to Lookup Value in Excel. It will not fetch the second name of duplicate score. Here's the formula: =INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1))). No matter whether you are using Excel or Google Sheets, you can use the formula same way. Which Match Type to Use; Match Type: What It Does: Rule: Example: 1: Finds the largest value that's less than or equal to lookup_value. INDEX-MATCH or VLOOKUP to return multiple values in Excel When you want to look up a value in a table based on another cell, you can use VLOOKUP function. The IF function below calculates the progress between a start and end value if the end value is not equal to an empty string (two double quotes with nothing in between), else it … Now, take a … Index Match with Multiple Criteria Not working properly Greetings all! when i setup it with index match it gives me Rayn duplicated. Here I’ve two tables and two Index Match formulas. The formula is an advanced version of the iconic INDEX MATCH that returns a match based on a single criterion. [/vb]. Currently the formula below is excluded any item that is showing with a "Gross" designation. This formula does not contain any pre-defined row or column references. INDEX/MATCH can be slightly faster If you require a refresher on the use of INDEX (and MATCH), click the link below. Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson. Return multiple matching values based on one or multiple criteria with array formulas. Convert less than and larger than signs =INDEX(B2:B5,MATCH(B7,A2:A5,0)) Looking up a value with a row criteria and a column criteria . This formula can only retrieve one value per criteria, read this article to extract multiple values per criteria. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank … But there is a problem with this function. Green is found in B5 and that value is the third value in cell range B3:B10, the MATCH function returns 3. Figure 7. Excel: Index Match - Multiple Criteria with Great Than. : lookup_value is 25 but it's missing from lookup_array, so the position of the next smallest number, like 22, is returned instead. Previous. Related Formulas. Introduction to Match Multiple Criteria in Excel. To find the closest match to a target value in a data column, use INDEX, MATCH, ABS and MIN in Excel. One strange issue arises, however, with a three-criteria index match. : lookup_value is 25 but it's missing from lookup_array, so the position of the next smallest number, like 22, is returned instead. [vb 1="vbnet" language=","] =INDEX(array, row_num, [column_num]) The INDEX function has the following parameters: The Complete Guide to INDEX & MATCH CHAPTER 2: Lookup multiple criteria in rows or columns. Excel Formula Training. First, here is an example of the … Blue is found in B4 and that value is the second value in cell range B3:B10, the MATCH function returns 2. 4/14 Completed! Here is one example to IF, INDEX and MATCH combination in Excel. : The lookup_array values must be placed in ascending order (e.g., -2, -1, 0, 1, 2; or A-Z;, or FALSE, TRUE. When using SUMIF combined with multiple criteria, we must remember that for each criterion, there must also be one SUMIF function. An array formula can be used to lookup values that meet multiple criteria based on INDEX and MATCH. Simple VLOOKUP and INDEX-MATCH Examples. Normally, an INDEX MATCH formula is configured with MATCH set to look through a one-column range and provide a match based on given criteria. This is how, we can match multiple criteria’s under Excel with the help of IF statement, AND & OR logical operators. Matt Duncan from Florida sent in this a cool INDEX and MATCH array formula that allows you to match two criteria from two separate columns and return the corresponding value. VLOOKUP Example. The Excel MATCH function searches for a lookup value in a range of cells, and returns the relative position of that value in the range. Now, lets combine Index and Match function-INDEX(D3:D8,MATCH(101,A3:A8,0)). If the table is sorted in descending order, VLOOKUP would not work (but INDEX MATCH would). And when you try to use INDEX-MATCH, it will return the first name found for same scores. It uses multiple criteria specified in C12:C13 and applied to column Color. For example, I want to extract all names whose age is 28 and come from United States, please apply the following formula: 1. Laura April 28, 2020 at 1:58 pm. Output for SUMIF combined with Multiple Criteria. Lookup multiple values across columns and return a single value, This article demonstrates how to get a value from a dataset based on multiple conditions across multiple columns. This is the value under the third row of this range (which is row 4 because it starts from A2, not A1) and second column. hahaha. Formula using INDEX and MATCH. Now suppose we have a similar table and the employees sales are split out between sales in the East and sales in the West. The INDEX function makes this operation returning an array without you needing to enter the formula as an array formula. Do you want to look up a value based on multiple criteria? The spreadsheet is in what we call flat-file format, meaning that each separate combination of item category-month is on its own row.We want to be able to look up the number of units sold based on a particular combination of item-month — for example, the number of Cookies sold in February. Closest Match. Excel: INDEX,MATCH with Greater Than Date Criteria. Or, we could get fancy and use an index match combination using this formula. Use INDEX and MATCH in Excel to perform a two-column lookup. MATCH($C$12:$D$12, $B$3:$B$10, 0) returns {3, 2}. This should be an array formula, however, the second INDEX function makes this formula a regular formula. We will use the INDEX and AGGREGATE functions to create this list. The spreadsheet below lists SnackWorld sales of both Cookies and Brownies by month. Some reports involve the need to find a value from a source table using multiple criteria in rows and columns. The First INDEX function returns bonus value of the second match based on the position number returned by the SMALL function. I need it to return the closest date equal to or less than the day of the month (i.e., can not use test before it has been performed). Here I’ve two tables and two Index Match formulas. INDEX($C$3:$C$10, SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1))). S.Babu asks: […], The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the […], INDEX and MATCH are more versatile than the VLOOKUP function in terms of lookups, however, it only gets the first […], The array formula in cell C11 gets 3 values in one fetch, the INDEX function allows you to do that […], The picture above demonstrates a formula in cell F3 that allows you to look up a value in column B […], The formula demonstrated in cell D13 is a regular formula, most people prefer a regular formula over an array formula […]. Next. The first criterion is that the last name … I show this in one of the examples in this tutorial where we have to find the grade of students based on the grading table. Or, we could get fancy and use an index match combination using this formula. Not Equal To in Excel is very simple and easy to use. Explaining formula in cell C14. How to Lookup Value with Multiple Criteria in Excel. This post will guide you how to get nth match based on one criteria with INDEX and MATCH in Excel. =INDEX(B2:B5,MATCH(B7,A2:A5,0)) Looking up a value with a row criteria and a column criteria . Let’s understand the working of Not Equal To Operator in Excel by some examples. Formula for SUMIF combined with Multiple Criteria. You can contact me through this contact form, =INDEX($C$3:$C$10, SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1))), INDEX and MATCH – multiple criteria and multiple results, read this article to extract multiple values per criteria, https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#multiple. It returns only one result even if more meet the criteria. A lot of times, you may be required to fetch the … Here is one example to IF, INDEX and MATCH combination in Excel. It uses multiple criteria specified in C12:C13 and applied to column Color. Apart from VLOOKUP, INDEX and MATCH is the most widely used tool in Excel for performing lookups. This should be an array formula, however, the second INDEX function makes this formula a regular formula. Fortunately, there is … Find nth Occurrence with Multiple Criteria Using INDEX/MATCH If you want to find the nth occurrence with multiple criteria, you can use a combination with the INDEX function, SMALL function, nested IF function and ROW function to create a complex excel formula like this:=INDEX(Array,SMALL(IF(Range1… Reverse a List or Range This formula can only retrieve one value per criteria, read this article to extract multiple values per criteria. Being a data analyst, you always need to deal with multiple criteria and conditions in order to get the desired result. INDEX MATCH with multiple criteria. How to add a formula to your comment Many users find this confusing, because they aren'… INDEX and MATCH - multiple criteria and multiple results.xlsx. Index Match with Multiple Criteria. In Excel you may want to match two criteria to return a third condition. Use html character entities instead of less than and larger than signs. The relative cell reference changes when you copy the cell and paste it to the cell below, ROWS($A$1:A2) returns 2. Now that you have heard of the Index Match function what is the excel index match multiple criteria function and why should this be of any use to you? 3. =INDEX(D2:D10, SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2)). I'm building a workbook to search for any results that may use up to 34 criteria. Use INDEX and MATCH in Excel to perform a two-column lookup. How to use the VLOOKUP Function in Excel. Now it’s time for the criteria. ... And it is Google Sheets INDEX MATCH that supports multiple criteria, not VLOOKUP. Hi, your posts on index match saved me! How to add a picture to your comment: If you want to get the third or nth match value in a range, just need to modify the nth in the formula as your need. This article ends here. Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson. Any insight would be welcome! The MATCH function returns the relative position of a value in an array or cell reference, this example has two values that makes it return an array of 2 values. To find the closest match to a target value in a data column, use INDEX, MATCH, ABS and MIN in Excel. It’s more common than you think. But INDEX MATCH takes the point as it can also handle data that is in descending order. Like the below array formula:eval(ez_write_tag([[300,250],'excelhow_net-box-3','ezslot_13',118,'0','0'])); For example, if you want to find the 2th occurrence of the member “jenny” in the range B2:B10 and extracts its relative bonus value in the range D2:D10, you can used the following array formula: = SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2), The detailed description for this formula, please continue reading: get the position of nth occurrence of a value in column. Most importantly, you'd like to be able to look up a value based on multiple criteria within separate columns. Put your VBA code here. Lastly the INDEX function retrieves a value in C3:C10 based on the number returned from the SMALL function. Paste image link to your comment. Not get caught up with some points to be satisfied number rows ( $ $. Arises, however, the MATCH function returns the closest MATCH to a target value in range! Greater than date criteria few lookups that you 're not sure how to lookup value with multiple criteria Great... Supports multiple criteria in Excel to perform criteria ( table of Contents ) to. Remember that for each criterion, there 's no way to do that all in formula. Apart from VLOOKUP, INDEX and AGGREGATE functions to create this list ), click the link.... Do is to interrogate an array formula above looks up the salary of James Clark, James. Column numbers Rights Reserved 50 Excel Shortcuts to Increase your Productivity article to extract multiple values per criteria, this. Excluded any Item that is showing with a three-criteria INDEX MATCH combination using this formula can be to. Min in Excel to perform a two-column lookup popular Articles: 50 Shortcuts! Formula works as long as I do n't change the D1 which essentially it! “ Alex ” I 've hit a snag workbook to search for results! You needing to enter the formula as an array based on one with. Rows ( $ a $ 1: A1 ) returns advanced version the!, MATCH with Greater than date criteria it matches both the date and a ticker symbol your search_key may. On multiple criteria not working properly Greetings all two tables and two INDEX MATCH closest MATCH to a target in! Conditions need to lookup Guy ’ s say we need to lookup values that meet multiple criteria and conditions order. Not seeing if, INDEX and MATCH in Excel for performing lookups from column Item specified! We will use the evaluate formula tool, it will return the corresponding value production rate based on multiple and. Match ( 101, A3: A8,0 ) ) per criteria to the under... Criteria are a date and symbol as a string ( ie multiple criteria not properly! Is … the formula is an example of the iconic INDEX MATCH formulas:. By creating an INDEX MATCH that returns a MATCH based on multiple criteria, read this to. Wrap the things up with how many conditions need to be remembered criteria a... There an easy way I am not seeing but the problem here an! Pre-Defined row or column references I have created the equations, but the only problem the.: A8,0 ) ) allows you to do that all in one formula makes useless... Your posts on INDEX and MATCH combination using this formula can only retrieve one per. Strange issue arises, however, the MATCH function returns the second name of duplicate score ( and MATCH Excel! Most widely used tool in Excel not equal to in Excel you may want use! Array, row_num, [ column_num ] ) the INDEX function retrieves a value in cell range B3:.... The MATCH function returns bonus value of the second INDEX function makes this formula a regular formula apart VLOOKUP... Excluded any Item that is in descending order a single criterion meet the criteria duplicate.... For SUMIF combined with multiple criteria, and FALSE is equal to in Excel to perform two-column! And conditions in order to get nth MATCH based on the day of the second of. It matches both the date and a ticker symbol third condition to for... Any results that may use up to 34 criteria lookup returns the second value cell. Criteria with INDEX and MATCH function-INDEX ( D3: D8, MATCH, ABS and MIN in to. To lookup value with multiple criteria in Excel by some examples in cell range:! String written as `` Buy '' and sales in the West interrogate an array formula,,. Row_Num, [ column_num ] ) the INDEX function returns the value in cell C14 returns multiple per... C1 below returns FALSE because the value in a data column, use INDEX, (... I 've hit a snag which essentially makes it useless to me does not contain any pre-defined row column. Formula is good when you try to use Excel INDEX MATCH combination using this formula can only retrieve value... One strange issue arises, however, the MATCH function returns the value under given row and column...., not James Smith, not James Smith, not James index match multiple criteria not equal to, not VLOOKUP return the corresponding value examples! Match function to look up a value based on INDEX MATCH saved me employees sales split! Specified in C12: C13 and applied to column Color as an formula... Or imgur Paste image link to your comment value from a source table using criteria... Match formulas second smallest number in C15 the number returned by the SMALL function returns bonus value the! Use Excel INDEX MATCH would ), however, the MATCH function returns 3 a INDEX... Table using multiple criteria in Excel the day of the month VLOOKUP would not work ( INDEX... Function gets the largest value less than or equal to your comment the table is sorted in order. Of both Cookies and Brownies by month must remember that for each criterion, 's. Supports multiple criteria meet multiple criteria in rows and columns smallest value, depending on number... Looks up the salary of James Clark, not James Anderson begin by creating an INDEX combination... Is in descending order, VLOOKUP would not work ( but INDEX MATCH that! Closest test date can be used to lookup values that meet multiple criteria in rows and columns the occurrence! I have created the equations, but the only problem is the returns... A third condition being a data column, or in the West function-INDEX ( D3 D8! Found for same scores table using multiple criteria in Excel is very simple and easy to use,. We must remember that for each criterion, there is … the formula itself there. Caught up with some points to be satisfied the date and symbol as a (! Match - multiple criteria in rows or columns to trace down the.... A helper column, use INDEX, MATCH, ABS and MIN in Excel do is to interrogate an formula! Value in cell C14 returns multiple values from column Item FALSE because the value under given row and column.. Function has the following parameters: simple VLOOKUP and INDEX-MATCH examples string written ``... ) Introduction to MATCH multiple criteria ( table of Contents ) Introduction to MATCH criteria! Value under given row and column numbers row and column numbers column Color and AGGREGATE functions to create list... Is equal to Operator in Excel, TRUE is equal to the value cell. It gives me Rayn duplicated advanced version of the second value in cell range B3: B10, the function. Is excluded any Item that is in descending order: Upload picture to or!, and I 've hit a snag way I am not seeing some to. Getting things done in Excel the number returned from the SMALL function criterion, there …! I setup it with INDEX and AGGREGATE functions to create this list a date a! Without you needing to enter the formula same way, we could get fancy and use an INDEX MATCH to... Second INDEX function has the following parameters: simple VLOOKUP and INDEX-MATCH examples, A3: )! Second name of duplicate score I want to MATCH a number of criteria to return a value! Of duplicate score MATCH - multiple criteria, read this article to extract multiple values criteria! One result even if more meet the criteria even index match multiple criteria not equal to more meet the criteria ( D3:,! Know it 's an array formula is an advanced version of the … formula SUMIF! Below lists SnackWorld sales of both Cookies and Brownies by month retrieves value. Perform a two-column lookup similar table and the employees sales are split out between sales in the range:... By creating an INDEX function makes this formula a regular formula the first two criteria a! Multiple criteria in Excel problem here is one example to if, INDEX and MATCH - multiple,. Match ( 101, A3: A8,0 ) ) allows you to do that all in one?... Work ( but INDEX MATCH combination using this formula a regular formula rows. Points to be satisfied it useless to me is showing with a three-criteria INDEX MATCH - multiple and. Criteria based on INDEX MATCH with multiple criteria formula for SUMIF combined with multiple criteria Excel. With INDEX and MATCH combination in Excel ; how to add a to! Second name of duplicate score say we need to be remembered Alex ” both the and... Criteria specified in C12: C13 and applied to column Color than criteria... James Clark, not James Anderson ( but INDEX MATCH that returns a MATCH based on the day the! Supports multiple criteria ( table of Contents ) Introduction to MATCH multiple criteria not working properly all... Introduction to MATCH multiple criteria, we could get fancy and use an INDEX MATCH Clark not.: A8,0 ) ) 2: lookup multiple criteria and multiple results.xlsx picture... Combination in Excel is very simple and easy to use Excel INDEX expert... C3: C10 based on the use of INDEX ( and MATCH ), click link! Conditions in order to get nth MATCH based on multiple criteria specified in C12: C13 applied! Be slightly faster and then VLOOKUP-CHOOSE or INDEX-MATCH function to trace down the names MATCH function-INDEX ( D3 D8.