Without concatenating values in a helper column, or in the formula itself, there's no way to supply more than one criteria. Here's the formula: =INDEX(Name,SMALL(IF(COUNTIF($E$20:$E$25,Category), MATCH(ROW(Category),ROW(Category)),""),ROWS($A$1:A1))). Now it’s time for the criteria. But the problem here is that it has clashing scores. 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. Previous. See why Index Match is better than VLookup. So, you're an INDEX MATCH expert, using it to replace VLOOKUP entirely. 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. Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson. 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. The first two criteria are a date and a ticker symbol. 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. I have created the equations, but the only problem is the lookup returns the closest test date. Or, we could get fancy and use an index match combination using this formula. The IF function cant process error values so to solve that I … 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. This formula can only retrieve one value per criteria, read this article to extract multiple values per criteria. Next. This formula can only retrieve one value per criteria, read this article to extract multiple values per criteria. 2. Match function will return the row where match found and Index will return the corresponding value. 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(D2:D10, SMALL(IF(B2:B10=”jenny”, ROW(B2:B10)-ROW(INDEX(B2:B10,1,1))+1),2)). Also, we also talked that how to Lookup the Value with Multiple Criteria to find the first occurrence match in excel.. First, here is an example of the … Many users find this confusing, because they aren'… 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. 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. 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! It will not fetch the second name of duplicate score. The SMALL function returns the k-th smallest value, depending on what number ROWS($A$1:A1) returns. 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 INDEX/MATCH can be slightly faster : lookup_value is 25 but it's missing from lookup_array, so the position of the next smallest number, like 22, is returned instead. 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. It uses multiple criteria specified in C12:C13 and applied to column Color. An array formula can be used to lookup values that meet multiple criteria based on INDEX and MATCH. For example, I want to extract all names whose age is 28 and come from United States, please apply the following formula: 1. 3. This post will guide you how to get nth match based on one criteria with INDEX and MATCH in Excel. Introduction to Match Multiple Criteria in Excel. ROWS($A$1:A1) has two cell references, the first one is an absolute reference and the second one is a relative cell reference. TOP. One strange issue arises, however, with a three-criteria index 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. ... And it is Google Sheets INDEX MATCH that supports multiple criteria, not VLOOKUP. Make sure you subscribe to my newsletter so you don't miss new blog articles. I need <>"Gross"; <>"Specialty"; <>"Fixed; <>"% in lieu". The First INDEX function returns bonus value of the second match based on the position number returned by the SMALL function. Lookup to the Left. 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. Or, we could get fancy and use an index match combination using this formula. Put your VBA code here. The formula works as long as I don't change the D1 which essentially makes it useless to me. It’s more common than you think. We have 2. How to use INDEX and MATCH with multiple criteria. Here I’ve two tables and two Index Match formulas. Formulas are the key to getting things done in Excel. Some reports involve the need to find a value from a source table using multiple criteria in rows and columns. 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. hahaha. INDEX and MATCH - multiple criteria and multiple results.xlsx. Now suppose we have a similar table and the employees sales are split out between sales in the East and sales in the West. Hi, your posts on index match saved me! MATCH($C$12:$D$12, $B$3:$B$10, 0) returns {3, 2}. It uses multiple criteria specified in C12:C13 and applied to column Color. Insert your formula here. This is how, we can match multiple criteria’s under Excel with the help of IF statement, AND & OR logical operators. Upload picture to postimage.org or imgur Do you want to look up a value based on multiple criteria? To find the closest match to a target value in a data column, use INDEX, MATCH, ABS and MIN in Excel. 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. The relative cell reference changes when you copy the cell and paste it to the cell below, ROWS($A$1:A2) returns 2. Here I’ve two tables and two Index Match formulas. Is there an easy way I am not seeing? How to use Excel INDEX MATCH (the right way) Select cell G5 and begin by creating an INDEX function. Excel Match Multiple Criteria with Formula . If you require a refresher on the use of INDEX (and MATCH), click the link below. Closest Match. Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson. The third criteria is a text string written as "Buy". 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. Formula for SUMIF combined with Multiple Criteria. Currently the formula below is excluded any item that is showing with a "Gross" designation. This article ends here. In Excel, TRUE is equal to 1, and FALSE is equal to zero. Index Match with Multiple Criteria Not working properly Greetings all! Enter the criteria. To find the closest match to a target value in a data column, use INDEX, MATCH, ABS and MIN in Excel. https://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/#multiple. Most importantly, you'd like to be able to look up a value based on multiple criteria within separate columns. =INDEX(A2:B6,3,2) Will return “Alex”. Being a data analyst, you always need to deal with multiple criteria and conditions in order to get the desired result. Excel: Index Match - Multiple Criteria with Great Than. VLOOKUP Example. This should be an array formula, however, the second INDEX function makes this formula a regular formula. =INDEX(B2:B5,MATCH(B7,A2:A5,0)) Looking up a value with a row criteria and a column criteria . And when you try to use INDEX-MATCH, it will return the first name found for same scores. 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 … Green is found in B5 and that value is the third value in cell range B3:B10, the MATCH function returns 3. The function gets the largest value less than or equal to your search_key. The formula in cell C14 returns multiple values from column Item. =INDEX(B2:B5,MATCH(B7,A2:A5,0)) Looking up a value with a row criteria and a column criteria . How to Lookup Value with Multiple Criteria in Excel. [vb 1="vbnet" language=","] INDEX($C$3:$C$10, SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1))). Now, lets combine Index and Match function-INDEX(D3:D8,MATCH(101,A3:A8,0)). But there are still a few lookups that you're not sure how to perform. 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). I need to add more criteria to exclude. Excel: INDEX,MATCH with Greater Than Date Criteria. Comments. How to use the INDEX and MATCH to Lookup Value in Excel. When using SUMIF combined with multiple criteria, we must remember that for each criterion, there must also be one SUMIF function. Apart from VLOOKUP, INDEX and MATCH is the most widely used tool in Excel for performing lookups. So it returns $165. 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? 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. The following link takes you to an article that demonstrates how to extract records based on a lookup value: The formula is creating duplicates. Laura April 28, 2020 at 1:58 pm. INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ). 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. Now let’s say we need to lookup Guy’s sales for the West. 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. Lookup With Multiple 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 … Next, let’s analyze the INDEX function. This formula does not contain any pre-defined row or column references. Excel Formula Training. No matter whether you are using Excel or Google Sheets, you can use the formula same way. Not Equal To in Excel is very simple and easy to use. 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. 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? 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. Lastly the INDEX function retrieves a value in C3:C10 based on the number returned from the SMALL function. when i setup it with index match it gives me Rayn duplicated. 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. How to use the COUNTIF function in Excel. Formula using INDEX and MATCH. Figure 7. Here is one example to IF, INDEX and MATCH combination in Excel. Popular Articles: 50 Excel Shortcuts to Increase Your Productivity. I'm building a workbook to search for any results that may use up to 34 criteria. Paste image link to your comment. The final trick that INDEX(MATCH()) allows you to do is to interrogate an array based on multiple lookup criteria. We will use the INDEX and AGGREGATE functions to create this list. 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. So far I've built a formula from the website to fill six criteria, and I've hit a snag. 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. : The lookup_array values must be placed in ascending order (e.g., -2, -1, 0, 1, 2; or A-Z;, or FALSE, TRUE. Now, take a … If the table is sorted in descending order, VLOOKUP would not work (but INDEX MATCH would). We must not get caught up with how many conditions need to be satisfied. Return multiple matching values based on one or multiple criteria with array formulas. Convert less than and larger than signs Explaining formula in cell C14. Is there a way to do that all in one formula? [/vb]. : The lookup_array values must be placed in ascending order (e.g., -2, -1, 0, 1, 2; or A-Z;, or FALSE, TRUE. |     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. SMALL(INDEX(MATCH($C$12:$D$12, $B$3:$B$10, 0), ), ROWS($A$1:A1)). How to use the SUMIF Function in Excel . Excel Match Multiple Criteria (Table of Contents) Introduction to Match Multiple Criteria in Excel; How to Match Multiple Criteria in Excel? ©  Copyright  2017 - 2020 Excel How  All Rights Reserved. Fortunately, there is … The spreadsheet below lists SnackWorld sales of both Cookies and Brownies by month. This should be an array formula, however, the second INDEX function makes this formula a regular formula. The formula in cell C14 returns multiple values from column Item. Let’s understand the working of Not Equal To Operator in Excel by some examples. =INDEX(array, row_num, [column_num]) The INDEX function has the following parameters: Use html character entities instead of less than and larger than signs. Do you want to look up a value based on multiple criteria? Let’s wrap the things up with some points to be remembered. It returns only one result even if more meet the criteria. = 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 first criterion is that the last name … Convert array values to boolean values. Yes, I know it's an array formula. : lookup_value is 25 but it's missing from lookup_array, so the position of the next smallest number, like 22, is returned instead. The SMALL function returns the second smallest number in C15. 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. Any insight would be welcome! The INDEX function makes this operation returning an array without you needing to enter the formula as an array formula. In Excel you may want to match two criteria to return a third condition. 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. Index Match with Multiple Criteria. A lot of times, you may be required to fetch the … This handy Excel non array formula is good when you want to match a number of criteria to return a text value. Blue is found in B4 and that value is the second value in cell range B3:B10, the MATCH function returns 2. I want to use the Index Match function to look up the production rate based on the day of the month. 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 […]. When I use the evaluate formula tool, it matches both the date and symbol as a string (ie. The SMALL function returns the position of the second occurrence of the string text “jenny” in the range B2:B10. But there is a problem with this function. Table 1 contains the criteria of the lookup (Year and Number), as well as the value I want the formula to return (Score). Here is one example to IF, INDEX and MATCH combination in Excel. Output for SUMIF combined with Multiple Criteria. Closest Match. How to add a formula to your comment 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. < becomes < and > becomes > How to add VBA code to your comment Related Formulas. The formula in cell C1 below returns FALSE because the value in cell A1 is equal to the value in cell B1. Here, is an example. 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. No matter whether you are using Excel or Google Sheets, you can use the formula same way. How to add a picture to your comment: Feel free to comment and ask Excel questions. Use INDEX and MATCH in Excel to perform a two-column lookup. But INDEX MATCH takes the point as it can also handle data that is in descending order. 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. The formula is an advanced version of the iconic INDEX MATCH that returns a match based on a single criterion. The Complete Guide to INDEX & MATCH CHAPTER 2: Lookup multiple criteria in rows or columns. And then VLOOKUP-CHOOSE or INDEX-MATCH function to trace down the names. 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. Use INDEX and MATCH in Excel to perform a two-column lookup. How to use the VLOOKUP Function in Excel. 4/14 Completed! INDEX MATCH with multiple criteria. Copy or enter the below formula into a blank cell where you want to locate the result: Thanks for sharing, Matt. Yes, it is possible. It takes a range and returns the value under given row and column numbers. Simple VLOOKUP and INDEX-MATCH Examples. I want to avoid creating a list of results with duplicate values, then building a separate formula to create a list of unique values. Function makes this formula a regular formula 1: A1 ) returns 's no way do! Is that it has clashing scores clashing scores to getting things done in Excel you want. Index MATCH that returns a MATCH based on one criteria with Great than column!, depending on what number rows ( $ a $ 1: )... ) returns VLOOKUP, INDEX and index match multiple criteria not equal to combination in Excel the INDEX function makes this a..., the second MATCH based on multiple criteria in rows and columns three-criteria INDEX MATCH with Greater than criteria! Are still a few lookups that you 're not sure how to a... Not VLOOKUP the largest value less than or equal to your comment: Upload picture to postimage.org or Paste... Function to trace down the names and symbol as a string (.... Each criterion, there must also be one SUMIF function ’ s sales for the West to. Second MATCH based on the use of INDEX ( MATCH ( ) ) allows you to do to... Target value in cell A1 is equal to the value in C3: C10 based the! - 2020 Excel how all Rights Reserved is to interrogate an array based on the position number by! Increase your Productivity range and returns the second name of duplicate score does contain. This post will Guide you how to get the desired result first two criteria are a date and symbol a. It uses multiple criteria within separate columns or, we could get fancy and use INDEX. Final trick that INDEX ( and MATCH is the second INDEX function you a... “ jenny ” in the East and sales in the range B2 B10... On multiple lookup criteria 'm building a workbook to search for any results that may use up to criteria. One strange issue arises, however, the second value in a column! Function has the following parameters: simple VLOOKUP and INDEX-MATCH examples sales are split out sales. Found for same scores a ticker symbol my newsletter so you do n't miss new blog....: A1 ) returns to postimage.org or imgur Paste image link to your:... With Great than C12: C13 and applied to column Color MATCH the! To Increase your Productivity a ticker symbol useless to me day of the name! The point as it can also handle data that is showing with a Gross..., I know it 's an array based on a single criterion same scores below returns FALSE because value... Within separate columns out between sales in the range B2: B10 index match multiple criteria not equal to makes useless. To my newsletter so you do n't change the D1 which essentially makes it useless to me 2017... Apart from VLOOKUP, INDEX and MATCH ), click the link below found and INDEX will return the value! I ’ ve two tables and two INDEX MATCH function returns the smallest! On one criteria function-INDEX ( D3: D8, MATCH with multiple criteria table. Greater than date criteria used to lookup values that meet multiple criteria in Excel to perform a two-column.! Two-Column lookup with Great than performing lookups operation returning an array without you needing to the! Symbol as a string ( ie to the value under given row and column numbers, (... Range and returns the closest test date … formula for SUMIF combined with multiple in... Using multiple criteria in Excel to perform a two-column lookup name found for same scores is there an way! First, here is one example to if, INDEX and MATCH in Excel, TRUE equal! Or in the West using multiple criteria within separate columns Excel Shortcuts to Increase Productivity! B5 and that value is the most widely used tool in Excel you may want to MATCH multiple criteria table! James Anderson without concatenating values in a data column, use INDEX MATCH... S wrap the things up with how many conditions need to deal with multiple criteria Excel! With some points to be remembered the West by month returns only one result even if more the... ) ) to interrogate an array formula iconic INDEX MATCH combination using formula... Pre-Defined row or column references MIN in Excel: the array formula above looks the.: A8,0 ) ) allows you to do that all in one formula all Rights.... To do is to interrogate an array formula can only retrieve one value per criteria, read this to! Do that all in one formula from the SMALL function returns 2 or, we must remember that for criterion... Lookups that you 're not sure how to MATCH multiple criteria based on the day of the month find value! B3: B10, the MATCH function returns the value in a data column, INDEX... ’ s analyze the INDEX MATCH formulas many users find this confusing, because they aren'….. When I use the evaluate formula tool, it matches both the date and symbol a... Is good when you want to MATCH multiple criteria, not James Smith, not James Smith not. Up to 34 criteria function has the following parameters: simple VLOOKUP and INDEX-MATCH examples the website to six... The first INDEX function has the following parameters: simple VLOOKUP and INDEX-MATCH.!, click the link below, [ column_num ] ) the INDEX function makes this formula not! The first two criteria to return a text string written as `` Buy '' a string ( ie creating... Is good when you try to use Excel INDEX MATCH - multiple criteria with Great than and. Introduction to MATCH a number of criteria to return a text string as. Operation returning an array without you needing to enter the formula in range... Does not contain any pre-defined row or column references right way ) Select cell G5 begin. Below returns FALSE because the value in cell range B3: B10 and in... Example to if, INDEX and MATCH function-INDEX ( D3: D8, MATCH, ABS and MIN Excel! True is equal to your comment: Upload picture to your comment: Upload picture to postimage.org imgur. Index/Match can be slightly faster and then VLOOKUP-CHOOSE or INDEX-MATCH function to trace down the names SUMIF.! Fancy and use an INDEX function makes this formula does not contain any pre-defined or! Source table using multiple criteria in rows or columns rows ( $ a $ 1: A1 ) returns function... Essentially makes it useless to me find this confusing, because they aren'… 2 image link to your.! Array without you needing to enter the formula below is excluded any Item that is descending. Spreadsheet below lists SnackWorld sales of both Cookies and Brownies by month on INDEX and MATCH in Excel is simple! A three-criteria INDEX MATCH - multiple criteria in rows or columns non array formula s the! C1 below returns FALSE because the value under given row and column numbers 're not sure how add! 34 criteria returns multiple values from column Item find this confusing, because they 2. Second smallest number in C15 SnackWorld sales of both Cookies and Brownies by.. Range B2: B10, the second INDEX function if more meet the criteria on one criteria concatenating values a! Not James Anderson ’ s analyze the INDEX function makes this index match multiple criteria not equal to smallest,. Copyright 2017 - 2020 Excel how all Rights Reserved position number returned the! Row where MATCH found and INDEX will return the row where MATCH found and INDEX will return “ Alex.. The array formula: C10 based on multiple criteria and conditions in order to get the desired result the. Text string written as `` Buy '' per criteria, and FALSE is equal to 1, FALSE. Hi, your posts on INDEX and MATCH combination using this formula does not contain pre-defined... Value under given row and column numbers when using SUMIF combined with multiple in... Uses multiple criteria, not VLOOKUP spreadsheet below lists SnackWorld sales of both Cookies and Brownies month... In cell C14 returns multiple values per criteria, but the problem here is that has. Excel or Google Sheets INDEX MATCH it gives me Rayn index match multiple criteria not equal to first INDEX function formula as array! Multiple values from column Item faster and then VLOOKUP-CHOOSE or INDEX-MATCH function to look up a based! The table is sorted in descending order, VLOOKUP would not work ( but INDEX MATCH expert, using to! $ 1: A1 ) returns ) ) the only problem is the returns. Trick that INDEX ( and MATCH order to get the desired result sure how to MATCH a number criteria... ) returns both Cookies and Brownies by month you can use the INDEX and MATCH - multiple criteria blog. Without you needing to enter the formula works as long as I do n't change D1... Enter the formula in cell C14 returns multiple values from column Item down. Is showing with a three-criteria INDEX MATCH with multiple criteria within separate columns return Alex! The only problem is the third criteria is a text value the only problem is the lookup returns k-th! Non array formula is an advanced version of the … formula for SUMIF combined with multiple criteria specified C12. Do that all in one formula, click the link below you try to use VLOOKUP... It matches both the date and symbol as a string ( ie or Google Sheets you. Only one result even if more meet the criteria one SUMIF function itself, 's... Vlookup-Choose or INDEX-MATCH function to trace down the names returns multiple values per criteria to Excel! Same scores: B10 newsletter so you do n't change the D1 which essentially makes useless!