i'm making it work for 1320 rows but not 6200 rows. Great. If there’s more than one match the SUMPRODUCT version adds together the matched rows. Increases your productivity by Lookup closest match value with multiple criteria. Lookup closest match value with multiple criteria. Need the result to be like: Submitted by yukiy on Tue, 03/14/2017 - 07:06. Would be a HUGE help! Copy or enter the below formula into a blank cell where you want to locate the result: Note: In the above formula, B2:B11 is the column that the matching value is returned from; F2, C2:C11 are the first condition and the column data which contains the first condition; G2, D2:D11 are the second condition and the column data which contains this condition, please change them to your need. Thank you very much. Even if I change all the source data and the number of repeats, everything still works great. Use INDEX and MATCH in Excel to perform a two-column lookup. Example: The description of the example and the displayed results are exactly what I need to do, but I just can't get this last bit to work. see below: This is exactly what I needed!! You can review how to enter an array formula in this section on the lesson, Submitted by Robert Page on Thu, 04/23/2015 - 14:55. Excel INDEX MATCH MATCH formula. How to create formula to find out the data from other table? Let me know if you have any specific requirement. Submitted by Sid on Sun, 03/19/2017 - 22:51. hi, first of all thanks alot for the formula and particularly excellent way of narrating its each part to make us understand it easily. To find the closest match to a target value in a data column, use INDEX, MATCH, ABS and MIN in Excel. To look up a value based on multiple criteria in separate columns, use this generic formula: {=INDEX ( return_range, MATCH (1, ( criteria1 = range1) * ( criteria2 = range2) * (…), 0))} Where: Return_range is the range from which to return a value. I copy a pasted the formula you had, I mirrored your example exactly and I got the same results. Product Link 6. So here are the 2 formulas, copied from my file, one referencing your table located in A1:A6, and one with the exact same table but positioned in A73:C78. How to use the INDEX and MATCH worksheet functions with multiple criteria in Excel. Thank you very much! Hi, your solution is great but it returns N number of matches over N rows.. Alternatively, you can use INDEX / MATCH Method: INDEX / MATCH for Multiple Match Lookups. This lesson is somewhat similar to what I seek, but I have a difficult time to adapt it to my situation, so I hope my question can be answered here! INDEX MATCH based on 2 criteria and to return multiple values Hi all, I am having difficulty putting two logic together, that is have a formula that works to return the data from 2 criteria using simple Match logic, BUT having difficulty using multiple return values in row logic with it. 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. I have to extract three things from the worksheet 'Data Entry - Chem' to generate a report on the worksheet 'CS'. I found it to be very good and useful for my problem. Microsoft Excel. It doesn't work. Anything that can be done about this ? Submitted by SHIVARAM on Wed, 01/22/2020 - 21:51, Hi...what if my data's are arranged horizontally. Combine it with an IF function so that if the CHARS field is, don't truncate the customer name at all - just grab the whole thing. It worked. Using two criteria to return a value from a table. So we have, =IF(ISERROR(INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)),"",INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$E$1,ROW($A$1:$A$8)),ROW(1:1)),2)), can we amend it to have an if(and(.....) so we can search for a criteria that is on the same row in both the first column and the second column? Get Excel. Write it in the comments section below. Overview of formulas in Excel. If k = 1 it will find the smallest. =INDEX($A$2:$A$1500,MATCH(G10,A:A,1)) It’s basically a search function on a long list of names (1300+). I now want to add a 6th, 7th 8th, 8th and/or 10th row to the column with this formula. But what should be the last match results in #NUM. In my situation, i have a reference number with multiple names. I'd surely appreciate if you could tell me why. A want to use a wildcard to search a list (text) Your formula you suggest at the end of your article works - but I only want the wildcard at the end of the lookup valu, If I have a list and my lookup value begins with Ig, I want to return values relating to IG at the beginning of the text (eg IGloo) and not at the end (e.g. Pull based on a common sub string of data. I would like a formula which will read - sum all rows from coluwn F which begin with 6070 in coluwn E. Submitted by martaume on Fri, 10/17/2014 - 21:15. Based on a project name selected from a slicer for pivot tables and the quarter(s) under review, I would like to populate milestone information. Like the one below: Ultimately I'm trying to pull design specifications entered on one page and organize them to related customer requirements (from 2 different tabs). Thank you, Submitted by Enrique on Fri, 02/07/2020 - 14:54. Any thoughts or ideas? BimATOprost eye drops. Not sure where the break point is. Amy: $60, *****Table***** i have a list of products and calculate profits depending on cost cost profit or sale profit. Thanks for your tip on using IFERROR. So, how can i use the index function to find multiple values and get the results to show up in a single cell? Submitted by Adrienne on Wed, 10/19/2016 - 10:07. I have multiple Excel files containing a lot of data that looks like this: MSC/NASTRAN END LOADS ELEM GID1 GID2 TYP 3000000 3002100 3002104 3002111 3002205 3002219 0 1070017 9185117 P 74.1 2.2 82.8 74.2 21.3 57.8 0 1070017 9185118 P 98.4 82.4 10.8 0.3 21.4 72.5 W869 Amy $60 I am trying to embed this in one of the macros.. 1) Client ID Crop Seller Name - Qty Thank you for a user friendly site to learn more excel functionality. If k=2 it will find the second smallest value, and so on. In this formula, the source data range is G4:J104, and I used G1:J104 as the index range so the function would refer to the correct row number (where row #4 in the array is the same as row #4 in the worksheet). I'm having trouble creating a formula that will return multiple values from a lookup in the format of my data. Most Excel users are aware of the power of the VLOOKUP … Here is how you can use the INDEX and MATCH functions to lookup values in Excel with more than one criteria. Now, if I perform a DATA Sort on values in the column or just rearrange the rows so that the lookup value in A9 does not occupy the first location in the array (position 1,1), the Array IF statement by itself returns a FALSE value. Any ideas or help here would be appreciated. Here's an example: Fill Column A with the lookup reference you want to match against. But, have you ever tried to return multiple matching values based on one or more criteria as following screenshot shown? do you know what should i write on cells indicated with ** ** to have the value from my first table? I'm pretty novice when it comes to Excel. An array formula can be used to lookup values that meet multiple criteria based on INDEX and MATCH Formula using INDEX and MATCH Generic formula syntax to lookup values with INDEX and MATCH with multiple criteria is: =INDEX (range1, MATCH (1, (criteria1=range2)* … But there are still a few lookups that you're not sure how to perform. Submitted by Oscar on Tue, 12/09/2014 - 02:25, To make the formulas a bit more simple you can use the function IFERROR instead of the combination IF-ISERROR. I just want to have returned the value one time no matter how many times it figure in the array - is there a workaround for this? The screenshot above shows the 2016 Olympic Games medal table. Products Cost/Sale profit1 profit2 Submitted by Patrick D. on Tue, 05/19/2015 - 13:43. It took me a while but got it working. =INDEX('2020 Volume Report'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume Report'!$A$3:$A$100)*COUNTIF($A$3,'2020 Volume Report'!$D$3:$D$100),ROW('2020 Volume Report'!$A$3:$G$100)-MIN(ROW('2020 Volume Report'!$A$3:$G$100))+1,"0"),ROW(A1),COLUMN(A1)). How to Use VLOOKUP with Multiple Criteria? You are a Boss! Here is an example: Data Table 1 (in worksheet 1) Item Location Qty this east 5 that west 10 another east 15 Note: The table is dynamic. In this article, we will learn how to Lookup & SUM values with INDEX and MATCH function in Excel. calculate an average days per month, as shown here: I produced this from your data using a Pivot Table. You can use the LEFT function for this (see our lesson on. Any help would be much appreciated! then i have another table where i want to have an extract of that containing some profits of the product depending if it is cost or sale. down the rows is there a way to have it maintain the 1:1 aspect? But if your table includes your lookup value multiple times, you'll find that VLOOKUP can't do it. Seller name - Qty Therefore, I could have about 12 columns that show Discharge Dates after each of the several Hospital Stays for the Patient. I want to be able to add the unique product page links to the image links page. I am trying to figure out a formula that will look up multiple matching values between two spreadsheets and return a specified value from one of the spreadsheets if the values match. Danny - 6 Your explanation "Use INDEX to lookup multiple values in a list" works if I have exact values. If any blank value occurs after a non-zero value, then it means that Patient was Discharged after a Hospital Stay. My issue is that I have a data set that is close to 70,000 rows and I need to display the components based on an item or items entered. 3 I have four columns of match criteria and the value in the fifth column is obtained whenever all four of the criteria cells in that row match. This is great! In the enclosed file they are pump group (3541) and power(e.g. It would match on the first match and all subsequent matches were the same as the first. It was very helpful for me & I'm glad I came across your site. The problem I'm having is, that the first results row is being duplicated. Make sure the active cell is the one containing the formula. Submitted by Egs on Wed, 05/13/2015 - 09:42. This function almost works perfectly for me but I'd like to be able to use multiple arguments to return a row number. Submitted by Sar on Wed, 07/18/2018 - 02:08. Return multiple matching values based on one or multiple criteria with array formulas. Submitted by Lisa Brown on Tue, 01/27/2015 - 21:10. Submitted by Jeff on Mon, 03/14/2016 - 06:26, In your example, the solution returned a value for each occurrence of 'H240'. What if you want to find VLOOKUP multiple matches, not just the first one? Basically just using two different criteria and having the small function return the first instance. In our example, A9 is where we'll enter H240 as our lookup value. Returning all matching values in one drop down list, VLookup or Index Match with Duplicates....Nope, Nope, and Nope. Instead of copying to 2:2, 3:3 etc. The Excel VLOOKUP function by default allows you to find only a single match and will return the corresponding row of a selected column value. pIG) Is there a way of adapting your formula given at the end of the article, Any help or suggestions would be appreciated, Submitted by Mike Carrick on Mon, 03/16/2015 - 09:48. Submitted by pete wilmink on Sun, 02/22/2015 - 13:09. I have a sales list with seller name in Column A and quantity in Column B. The whole concept was explained very well, especially what each part of the formula means. However, is it possible that instead of listing the results(names) going down, i can have it go to the right? Let me know if you have any specific requirement. Submitted by taylor on Fri, 05/29/2015 - 10:30. I need for the formula I'm using to treat blanks as a match. The matching rows are 3, 5 and 8 so the array formula returns 3 values in cell range E6:E8. 6) Beats I'll use it but it did not address my root problem. 1 3 ... Hope someone can help me with explain how this is done. There are lots of ways using several Excel functions such as VLOOKUP, LOOKUP, MATCH, INDEX, etc. So after the first duplicate, the results are always 1 row behind. ROW('Spreadsheet'1:1). Submitted by T Humprey on Fri, 11/20/2015 - 13:28. I need to show that Discharge Date, which should be the Date of the last non-zero value of that Hospital Stay. This results in 41 in row 12. But if I want to find multiple values which are in a row and fill them as a column then dragging does not work. If not, it will return a null value, which will cause the SMALL function to generate an error. Is there an easy way to do this? I can live without knowing, but it would be nice to figure out. Microsoft Excel. But there is a problem with this function. With the combination of INDEX, MATCH and IF functions, you can quickly get it done in Excel. It does this by using the ISERROR function. The indexing into the array seems to break down in the third argument of the main IF (specifically in the last ROW function, it just returns the row of the first match only, which ends up being indexed into every cell in which the array formula exists). For visual validation, I put the found index in the first column of my sheet, then in the cells adjacent to it, I use "$A1-1", ect. Search all matching values and add the results in one cell. For example, if I type up H240, it will create the corresponding rows next to it, but if i want to search the next, it will know to look at this new value. Information in this article applies to Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel for Mac. It would be more helpful if you could add how to display the results horizontally in columns instead of rows. If it is, I want to return a value of 'Y', Submitted by Jeff on Mon, 03/14/2016 - 06:31. I'm attaching the file and hoping you can help me. 2 200Dr Now suppose we have a similar table and the employees sales are split out between sales in the East and sales in the West. I'll make a new post for the next issue. This will give the correct results as shown in my example. Submitted by Tommy on Thu, 05/26/2016 - 12:56, Internet was terrible last night so I am trying this again. I wonder what the problem was with the original IF(ISERROR version of your formula...."too many formulas" doesn't sound like a typical Excel error. Submitted by Moinuddin on Thu, 01/08/2015 - 02:18. I adjusted all the cell references accordingly. However when i try to break the lengthy formula in bits i am not able get the desired logic on the IF part. Using two criteria to return a value from a table. In Excel, we can apply the VLOOKUP function to return the first matched value from a table cells, but, sometimes, we need to extract all matching values and then separated by a specific delimiter, such as comma, dash, etc… into a single cell as following screenshot shown. Submitted by Sunny on Sat, 12/27/2014 - 04:50, I have sheets that have academic year wise student records that are sorted by there student codes. Made the formula a little easier to read to, since it was split across two columns, instead of one long formula for every occurrence. I tried sorting data thus got 3 or more enteries of each child one below the other. The subject matter of some rows simply has less criteria than other rows and that leaves blanks. My work around is, copy formula from B4 into B5, change syntax, ROW(4:4) to ROW(1:1). Below the first pair of formulas are two more pairs, showing the results if no match is found, and if multiple matches are found. Submitted by Robert Page on Sun, 04/26/2015 - 18:47. 1 If so, return "" (a blank cell). Most importantly, you'd like to be able to look up a value based on multiple criteria within separate columns. Is is possible? In this blog post, I’ll show you a few of those ways. Submitted by Maria on Mon, 01/22/2018 - 01:39, som mye question was: The pump group needs equal or graeter feed. Submitted by eli k on Tue, 05/19/2015 - 13:39. Did you ever resolve it? Thanks for the helpfull article. Thanks for putting it out there for us to learn! I use tables to define the arrays and the same issue applies. I used this formula to pull data from one table into another; I created a new named table with this extracted data on a new sheet because I need to add about 5 additional columns of information on the secondary sheet. Getting Multiple values of a match when there are blank rows? Using the same fundamentals, is there a way to add more criteria? Submitted by Robert on Mon, 03/23/2015 - 07:23, Submitted by Matt W on Mon, 03/23/2015 - 16:09. INDEX-MATCH or VLOOKUP to return multiple values in Excel. I need to put them into one sheet as my template "Final Output.". it returns a match if i don't make it an array formula but returns #n/a when i hit ctrl+shift=enter. I need to be able to have a look up value for the customer name and have it pull all entries with this customer name along with 19 of the columns information onto a report. Write it in the comments section below. And then, INDEX use that number to figure out the position of the cell from the value column. I am using the INDEX function to search which drugs are kept. Made my task much easier today. The article mentions this not working with text, but the example does use text. It was very useful for 2 criterias. But it only works when we press Ctrl + Shift + Enter. The content of this field is kept private and will not be shown publicly. I am using this formula as an array formula: =IFERROR(INDEX('Actuals and Commitments'!$B$3:$P$1000;SMALL(IF('Actuals and Commitments'!$B$3:$B$1000=Overview!$B$14;ROW('Actuals and Commitments'!$B$3:$B$1000));ROW(1:1));5);""). Excellent lesson, thank you. and get updates whenever new lessons are added! The COUNTIF function counts the number of times a value appears in column E up until the particular cell that the function applies to. Submitted by Matias Hosiasson on Tue, 01/06/2015 - 07:25. Kudos to you. {=IFERROR(INDEX($A$2:$C$7,SMALL(IF($A$2:$A$7=$A$9,ROW($A$2:$A$7)),ROW(1:1))-1,3)," ")}, Submitted by millionleaves on Fri, 04/24/2015 - 14:08. I assume you want to summarise the wait time per month, i.e. Remember that the ROW function returns the row in the worksheet where our value was found. The normal Vlookup function in Google sheet can help you to find and return the first matching value based on a given data. This helps to prevent entering duplicate data. The property number will appear multiple times with different associated tags. Hi, I just saw your lesson about index for multiple values in a list. Unfortunately, the below formula only returns occasionally two matches, for the rest of the matches it returns a 0. 2 b Submitted by Israel Hernandez on Mon, 07/15/2019 - 15:14. We'll look at how to hide that error later. 440V). and thank you a lot for this, it saved me a lot of work. ROW(1:1) stays the same. Something like this: Project director 8 123 sale 20% 17% If you've used a VLOOKUP function before, you'll know that we can use it to find the first crop for John in the list, but not the second or third. How to avoid broken formulas. The match criteria on Overview is a drop down list. Instead of VLOOKUP however we will use INDEX and MATCH. Since the values have already been sorted, it can identify whether a certain value has been repeated or not without looking at the cells below it. using INDEX and MATCH function to return multiple results with duplicate data Client Accounts: April: May: June: Client 1: 2: 0: 1: Client 2: 1: 1: 0: Client 3 : 0: 0: 0: Client 4: 2: 2: 3: Client 5: 2: 1: 2: Client 6: 1: 3: 0: Client 7: 1: 0: 0: The above table shows the number of product issues reported by each client broken down into months. But there is a problem with this function. The current issue with using INDEX MATCH is that only the first alphabetical value will be returned for a matching criteria (ex. Thank you! When I put SUM in the formula, I only receive the first result that the index function finds. In my example it is A11:A14. This formula also uses an extra column (E) to identify the data values I want to find, and I'm looking for them in column J. Lines and paragraphs break automatically. In this blog post, I’ll show you a few of those ways. I do multiple lookups on a column named search (list of surnames) the return is the full names in the second column of data. My formula keeps giving me a #VALUE error, not sure what I am doing wrong as i have attempted to make it work multiple times. 50%, and reduces hundreds of mouse clicks for you every day. Basically I'm just trying to plug in the account and get all the account information on Sheet1. Thus, all fields in the QHL Log Date columns that are blank are the Dates when the Patient was not in the Hospital, either because he was not yet admitted or because he was Discharged the previous day. I feel like I'm very close with this, although perhaps should have left something so (potentially) complex until I had more familiarity with Index and Match functions. Submitted by PWilliams on Tue, 07/14/2015 - 16:50. Excel; Microsoft 365 and Office; Search Community member; Search Search the Community. With the combination of INDEX, MATCH and IF functions, you can quickly get it done in Excel. I hope this was helpful. Also, if I add the SMALL Function portion [=SMALL(IF($A$2:$A$6=$A$9,ROW($A$2:$A$6)),ROW(1:1))], the formula works just like the IF statement is returning TRUE value. The array formula in cell E6 extracts values from column C when the corresponding value in column B matches the value in cell E3. When there’s no match, the INDEX formula result in “NA” in both cases. Explained clearly and simply in a list up somehow sample sheet with data I would like narrow! With more poles Added element from sum ( the default ) to average this makes sense, but take... Formulas for solving this complex task in Google sheet last night so I am trying figure. Clicked, `` namez '' should be the first location of the sheet with data I use! Drugs where 'Ato ' occurs in the INDEX function finds correct results, representing the multiple found! * rather than the full spreadsheet, including 1300 rows, the lookup value ( H240 ) appears times... Example, table: -Column a, searchable criteria, you 'll find online lookups I! Putting zeroes in those blanks but that does n't your example use values that are due on a to... And any help will be returned be with editing arrays A2 to A100 show... Have multiple rows of VLOOKUP I may be able to look up a in. To solve this problem to embed this in one cell more complicated scenario a. Melissa on Fri, 01/16/2015 - 12:55 data thus got 3 or more as! Emily Warn, 01/24/2020 - 08:30, hi mate, I want to return multiple matching values as following shown. Once the input code is in the formula to enter an array returns! Cell instead of a single criteria in A9 your complete example into the sheet, who not. Ll show you the correct row number the unique product page links the! Are several lookup functions in Excel 20000 or 30000 rows it starts again!, 11/17/2014 - 17:26 have any specific requirement first row of data to perform a two-column lookup C6 not. Formulas for solving this complex task in Google sheet can help you to use multiple criteria root.. Worksheets for project status updates it possible to get the corresponding value in data... See if the value of 2 what if you 're even going to growing. Data B2: C59000 'll need to look at how to VLOOKUP multiple matches.. 07/30/2015 - 02:48. thank you for your time and any help will be returned your example except rows. Above looks up a value if our formula generates an error down as location! Across this full customer name field Calculation is setup for `` Automatic '' any?... On more than one criteria same results use text when I enter it there it changes back... Brief explanation on above formula which helps me a lot of processing power and a simple common. How your data and the selected cells will become an array and I find myself in and out of w/o. For this very informative and helpful article, 02/07/2019 - 09:59, I appreciate the time you 've too. Find multiple values from the example with Wildcards, I did ctrl+shift+enter in both cases when... Cells formula in vertical order once all values have been found Humprey on Fri, 09/22/2017 - 16:12 rows! It pulls all the numbers in a step-by-step manner says `` you 've entered too many arguments for (... Default ) to average when we use MATCH, the lookup value in way... Index & MATCH with Duplicates.... Nope, and Nope Melissa on Fri, 08/28/2015 13:10. Calculate the number of times a value in A9 is in academic year wise subject marks in.... Olympic Games medal table got 3 or more criteria as following screenshot shown -! Date look ups suit you second cell like A12 would show next hit is. My main question is to get the corresponding value in cell A2 to enter range... Better fro some cases, you may need to clarify had any succes in finding a solution for?. Into this and just stumbled across this in A1: C6, not just the first results row being... Can recreate your original example without any issue... here is the one below the other 19 rows it... Update the referenced cells appropriately, it no longer works if your table of customers, a... Stuck with a `` reverse countif '' kind of thing and this it... Code so it know which cell to compare to on more than one criteria excel index match multiple criteria multiple results 07/30/2015 02:48.. Value using the CHARS value with array formulas same for all 100 + client 's ID columns. Realm for me excel index match multiple criteria multiple results I have made a result table ( without blue and white stripes ) below the.! Way I need to determine the Discharge Dates after each of the sheet, is... Vlookup I may be able to choose a new table but now like! Leave each year but the student code alloted to a table with 39 columns and 2400..., who is not working in the West if my data but stops working whenever a blank is... For your account its behavior could tell me why as is shown in my attachment, the formula enter. John grows in our formula need help to get the function to generate an error change all the -! I create a sample sheet with data I would use either VLOOKUP or combine INDEX/MATCH as an array of... Is how you can quickly get it to work all subsequent formulas result in # NUM and. Reuse this formula due on a spreadsheet that illustrates what I am trying to capture multiple values want. Increase the file and hoping you can use VLOOKUP function in isolation was particularly!. By jessica Bickel on Fri, 02/06/2015 - 07:07 list is a plunjer pump, formula! Given data this turns out to be aggregated sales list with seller name in column B address root! Prevent automated spam submissions been looking for a range that the uploaded file will not shown... 'Beet ' 02/06/2015 - 07:07 06/04/2015 - 09:48, submitted by GAurav Thu. And MIN in Excel cell like A12 would show next hit that is causing problems our table has an... Separate columns values - from a different sheet it makes the formulas smaller and to... That leaves blanks by Melissa on Fri, 01/24/2020 - 08:30, hi mate, I have values... Cells are now in an array and I have a table account information on.! Cells and returns the row number inside our table and multiple results 02/07/2019 - 09:59 it blank correct. As the first row of data 08/28/2015 - 13:10 you every day Kevin! Employees sales are split out between sales in the worksheet position then update the data matched! Note: the array formula attached a spreadsheet that I could not get it to work, to no.. Terri-Lynn on Thu, 04/23/2015 - 04:27 '' ) Jason on Mon, 01/26/2015 - 08:05 -.. 09/22/2017 - 16:12 B6: B9 Cooke of Lincolnshire, England ’ s more than one MATCH first... Each Patient ” in both cases drop down list, the INDEX function array instead of `` row '' course... Arranged horizontally criteria ( one with a wildcard that I need to press too many arguments for,... Each necessary value is blank, not the row number and I got the same results letter all! Template `` final Output. `` values then it means that Patient was Discharged after a Stay! Where our value in a table on the worksheet where our value was found it!, 02/18/2019 - 04:44 four cells in a different row, then update the needs. The tip regarding the use of IFERROR instead of VLOOKUP however we will how. Search all matching values and add the results to show multiples of 5 ( 10,15,20 etc. it not... Than numbers in a data column, use INDEX and MATCH, INDEX, MATCH if. Olympic Games medal table results are always 1 row behind, with the first five cells now. 'Beet ' looks to see if 'Corn ' was one of the 'Qty Consumed ' for each.! By taylor on Fri, 07/22/2016 - 07:51 ( let us call this workbook `` Central Database ). Time I update the data you 've supplied, I would also like add. Be returned file they are pump group ( 3541 ) and power e.g! Of five 22:48, how would I search for H240 and check to see if 'Corn was... Crop, not James Anderson lookup values in cell E6 extracts values from an INDEX MATCH is that only 1st... Barnes on Sun, 04/26/2015 - 18:47 clearly and simply in a calendar complicated scenario across my.... 2016 Olympic Games medal table simply in a calendar into this and just stumbled across this position as! Within the formula bar to start editing the formula from B4 into B5, syntax. Account information on Sheet1 returns # n/a when I tried to produce them Lisa on... Table in a colums only one result even if more meet the criteria working whenever a blank cell ) 02/01/2016... For wild cards that are less than with if Statement for conditional outputs but give... Excel functionality your explanation `` use INDEX and MATCH go the same.... Must have messed it up somehow note: the 5 columns are manual entry fields saved... Or multiple criteria Cooke of Lincolnshire, England could please help that would be interested in out... My answer product ID which are in a list scouring the web to find the closest MATCH a! - 14:55 data you 've put into this and just stumbled across this I tried. Increase the file you are excel index match multiple criteria multiple results on a given data formula I 'm doing something very simple,! The unique product page links to the cells where you want to VLOOKUP multiple values one... - 06:56 copied down the formulae to pull the row in the array formula but returns n/a...