Quick Navigation
Choosing Criteria and Output Data
We want to set our criteria as the minimum Combined MPG each car must have. For each vehicle that qualifies, we will want to list out the basic information about the model and its mileage. The criteria will be Column H, circled here in red. The output data will be Columns B through H, circled here in green.
Thanks, very well explained.
What if I want to use multiple criteria to fill out a table, for example, MPG>=45 AND compact cars?
A quick/easy solution would be creating an aux column in the full table that checks both criteria and returns “true” if the current row meets both criteria and do how they explain in this article using this aux column as the single criteria = “true”.
Great solution, but still struggling to understand how it works!!!!
To explain my understanding thus far, I understand the if error, index and if parts of the formula. I think I have the Small and Row elements sorted as far as I can see they are just returning the next row in the original data that meets the criteria.
However the ROWS part has me confused, as I discovered, by accident that I could use the row references in the sheet the original data was held in, or row references in the output sheet. Indeed, I found that I could reference a totally different sheet in the workbook and got the right answer, provided the row and cell reference weren’t changed – ie only changed the sheet name.
Any comments or explanation, appreciated.
Thank you
Hi hansetom,
ROWS just returns the number of rows in an array. For example, ROWS(A1:A3) would return 3, since there are 3 rows in that range. It would also return 3 for any other range of 3 rows, like ROWS(Z23:Z25).
The role it plays in the sub-array formula is to tell SMALL which row of the sub-array to display, so as long as the first entry is anchored with $, it will work. When you paste the formula down to build the new list, it will automatically change the second entry to expand the ROWS statement.
For example, in the first row (2), it might read ROWS($B$2:B2) and return 1. Copy and paste that into row 3, and the formula will now read ROWS($B$2:B3) and return 2, giving the second item in the list.
Hope this helps!
Andrew
Got it – though I note that it still works, even when I reference a completely unrelated workbook – provided as you say that the first entry is anchored.
When you say in your final paragraph …” read ROWS($B$2:B3) and return 2, giving the second item in the list.” you mean the second compliant value from the original array.
Thanks for a great solution!!!
Hansentom.
Hi,
what if i want to filter one column using 2 criteria instead of 1?
example:
=SUM(OR(Data!D2:D595=”Del”,Data!D2:D595=”Urgent Delivery”)*(Data!U2:U595=”No”))
Basically my column to filter is column D and the criteria in it are:
1) = “Del”
2) = “Urgent Delivery”
the output of above is not giving an error bu is giving wrong result.
Hi Cobra,
Right now, the formula is just checking for matches with A2 (our desired MPG number) in the IF statement, but you could add conditions by using AND() and checking for multiple criteria.
Andrew
Apologies, folks. The AND() function is actually not capable of handling Array Formulas. To add multiple criteria, you must multiply the conditions. For example, if we want to set minimum MPG (in cell A2) *and* maximum displacement (set in cell B2), we could do this:
IF( (‘Car Data’!$H$2:$H$1156>=’Filtered List’!$A$2) * (‘Car Data’!$D$2:$D$1156<='Filtered List'!$B$2) , ... Each condition is in parentheses, and they are multiplied. This works because true/false statements in Excel work like 1s and 0s: True * True = True (1 * 1 = 1) True * False = False (1 * 0 = 0) False * False = False (0 * 0 = 0) You can set any number of criteria in this way by multiplying them together. Only when all conditions are true will the row be included in the list.
Thanks. that really helped. I had a similar problem with multiple criteria. I got round it by adding a column to the table using IF, AND & OR statements to give me 1 or 0. I then used that column for my filter (IF) part of the array formula (see line 3)
=INDEX(RAW!O$3:O$429,SMALL(
IF(RAW!G$3:G$429=1,
ROW(RAW!O$3:O$429)-ROW(RAW!O$3)+1),ROWS(RAW!O$3:O3)))
What is you want to return a list of cars with 35-45 mpg? I cant get that to work using multiple criteria
Incredibly tricky ! Nice move with the multiply option.
Thank you!
I’ve been looking for something to replace the old MS-Query method of pulling records from a table, and this did the trick.
Here’s how I solved the multiple criteria- The IF statement is an array, so multiply by a second array to get all of the 6-cylinder cars:
if( (‘Car Data’!$H$2:$H$1156>=’Filtered List’!$A$2) * ( ‘Car Data’!$E$2:$E$1156 = 6 ) ,
Hope it works for you- great site and thread!
Hi Steve,
Tried your solution as well AND/OR and still not working, any ideas?
I also tried but could not get the multiple criteria to work… HELP lol
Is there any possible way to add OPTIONAL multiple Criteria
For example all cars that are 42mpg that are also “Compact” AND/OR “6 Cyl”
where you can search compact on one search then 6 cyl on the next
How could you add condition to it. Basically instead of searching 45 MPG +, how could you look for 45 MPG AND 40 MPG and add them both to the list? I ask because I have modified this for a stat sheet and I want to look up multiple positions. In my example instead of only being able to look up the RB, I want to look up RB AND WR and add them to the same rolling list as they have similar stats for comparison.
You can define conditions however you like. The current formula uses a greater-than-or-equal-to condition (>=). If you want it to equal 45 exactly, just use the equal condition (=).
As I explained above, True and False work like 1 and 0, so if you need an OR statement, you can add conditions and compare them to zero.
True + True > 0 –> True (1 + 1 = 2 > 0)
True + False > 0 –> True (1 + 0 = 1 > 0)
False + False > 0 –> False (0 + 0 = 0, which is not > 0)
In the example you provide, assuming 45 is in A2 and 40 is in B2, this equation will work:
(‘Car Data’!$H$2:$H$1156=’Filtered List’!$A$2) + (‘Car Data’!$H$2:$H$1156=’Filtered List’!$B$2) > 0
My question is along the same line as Cobra’s.
I have added the AND() function to include a date range and a text descriptor (XX>=$B$1, XX<$B$2, YY=$D$1), as soon as I add the AND function after the IF, it immediately returns the date before the start of the range. Then when I go to drag the array function down, all the cells become blank.
Any thoughts?
I am having the same trouble with an embedded AND function. It works fine with the IF, but breaks with the AND.
Any suggestions?
Hi folks… Please see my response to this comment for an explanation for how to use an AND condition with this formula.
For anyone who is interested, there is a good conversation about this technique happening over at the Mr. Excel forums: http://www.mrexcel.com/forum/excel-questions/767607-extract-list-values-filtered-criteria-sub-arrays-tutorial-help.html
Thanks a lot! This is hugely helpful.
I have adapted your formula to my project and it works great.
However, i don’t seem able to sort the newly created list. i.e. If you try to sort the newly created list by City mileage vs combined, the equation breaks.
Any suggestions?
thanks,
Nick
Hi Nick,
Great question! Because the list is generating from the original source data table, it can’t be sorted independently. If you sort the original source table by your criteria, it should be reflected in the filtered list as well.
Good luck!
Andrew
Is it possible to use and OR function instead of an AND? I have been trying to get the list to return all rows that equal X or Y in the criteria column. When I add the OR statment inside the IF it now returns all rows (even those that don’t equal X or Y). Thanks!
I am wondering the exact same thing. It does not seem to work.
Hi folks! See my reply to this comment for an explanation of how to implement an OR function.
Thanks so much for the step-by-step. In my data for certain rows there may not be any information. The formula seems to pull these empty cells in as “0”. Is there a easy way to pull them into the new list as blanks?
Sorry realized my question may not have been clear here is an image that help illustrate what I’m talking about (for example in the image: some rows of data does not have a defined launch date or sales and both of these are coming in as “0”, is it possible to make them come in as blank instead?)
It is possible, though the code is slightly messy… You have to check for the zero, then replace it with a blank, like this:
IFERROR( IF( entire_INDEX_formula_inside_the_IFERROR_goes_here = 0, “”, entire_INDEX_formula_inside_the_IFERROR_goes_here ),””)
I am having trouble figuring out how to exactly wrap my formula into what you have. I have your formula’s work for me like this:
=IFERROR(INDEX(OG!A$2:A$14469,SMALL(IF(OG!$AJ$2:$AJ$14469=Grid!$B$1,ROW(OG!A$2:A$14469)-ROW(OG!A$2)+1),ROWS(OG!A$2:OG!A2))),””)
I am not sure how it wrap it in the new if error for searching for the zero.
I have successfully used this lesson but am still having trouble with “0” returned for blanks in my master list. Due to some date formatting this creates a messy spreadsheet. I cannot seem to insert the fix already mentioned in this thread into my formula:
=IFERROR(INDEX(‘2015 work ‘!A$2:A$156,SMALL(IF(‘2015 work ‘!$P$2:$P$156=’Smith’!$B$1,ROW(‘2015 work ‘!A$2:A$156)-ROW(‘2015 work ‘!A$2)+1),ROWS(‘2015 work ‘!A$2:’2015 work ‘!A2))),””)
Please help. Messy formula is a non-issue at this point. Thank you!
Hello,
Has anyone found an answer to this? I, too, am having this issue. I have three sheets that have my client’s data, including a start and end date. I have created a new sheet for each month and have the formula pull over the clients that are active for that month.
Because my client data sheets have a formula to figure out their end date, the monthly sheet in which I run this array formula returns a 0 instead of blank. UGH… it’s so frustrating! I wish I could wrap my head around how to fix this:
Any help out there?
This was extremely helpful and worked perfectly to solve my problem. Thank you!! Now that it works in Excel I tried uploading it to Google spreadsheet so I could share my doc, but none of the array formulas worked. Are you able to help with that?
Google Sheets is not my area of expertise, but maybe another Excel Tactics reader knows of a workaround?
You can share an Excel file on GoogleDrive as-is (keeping all Excel format), just the recipient has to download the file before they can open it. In GoogleDrive, click the cog icon, choose Upload Settings, and untick “convert uploaded files to GoogleDocs format”. Maybe you already know, but if not, this could be a workaround.
Great!
Impressive.
I still don’t get all the details (though I tried to decompose intermediate formulas), but this works. I’ll dig this further.
I could use such sub-lists to feed mail merge (! not sure how the menu is named in english winword). It just wants to generate mails for the empty lines, hidden by the IFERROR(…;””), but it’s OK, I won’t print the empty pages 🙂
Many thanks, today I learnt something I did not suspect at all in excel.
Regards,
Excellent explanation. I used this page to initially learn array formulas, but now I find myself stumped. I need a formula that does two things:
1) Returns text values based on one criteria; a calculated date,
2) Does not return duplicate values
I have two formulas, one does one step and the other does the other step, but cant do both and I just cant seem to figure out how to put them together. Can you please help?
formula 1: =IFERROR(INDEX(Log[Volunteers],SMALL(IF(Log[Week Ending]=Summary!$B$2,ROW(Log[Volunteers])-ROW(‘Log’!B$8)+1),ROWS(‘Log’!B$8:’Log’!B8))),””)
Formula 2: =INDEX(Log[Volunteers], MATCH(0, COUNTIF(Summary!$B$4:B5, Log[Volunteer]), 0))
Like I said, they both work, but what I want is a formula that combines the effects of both of these: a filtered results with no duplicates. Once I have that I will use SUMIFS to get what I need in the remaining cells, which will be a total amount of hours each volunteer worked during that selected week (which is determined by the Week Ending column).
All of this needs to update automatically when the criteria date changes and more entries are added, so Advanced FIlters are out of the picture.
Please tell me you can help!
I just found this and it appears to work after I modified the references for my application.
=INDEX(B$2:B$15,SMALL(IF(N(FREQUENCY(IF(A$2:A$15=E$2,MATCH(B$2:B$15,B$2:B$15,0)),MATCH(B$2:B$15,B$2:B$15,0))>0)>0,ROW(B$2:B$15)-ROW(B$2)+1),ROWS($1:1)))
I hope this helps someone else because this was actually a pain to find!
thanks JackedUp! I’ve been trying to adapt your formula on my spreadsheet, but would really love if you can copy your data here so I can fully understand? thanks heaps!
I thought I understood this, but apparently not. I attempted to adapt the formula to my data with little success. I took a step back troubleshooting and tried something much simpler. Using the downloaded data, Instead of querying on Col H, I tried to query on Col K (# Gears). I changed
I the formula as follows:
This is row 5 of Filtered list
=IFERROR(INDEX(‘Car Data’!B$2:B$1156,SMALL(IF(‘Car Data’!$K$2:$K$1156>=’Filtered List’!$A$2,ROW(‘Car Data’!B$2:B$1156)-ROW(‘Car Data’!B$2)+1),ROWS(‘Car Data’!B$2:’Car Data’!B2))),””)
only changing the if($H>= to $K>=
Not only does this not return the right results. After copying it over and down, Row 1 contains data where gears !>= my number, but the subsequent rows return nothing. Actually then end up false, so they are blank.
This is what’s on row 6 of FilteredLlist
=IFERROR(INDEX(‘Car Data1’!B$2:B$1156,SMALL(IF(‘Car Data1′!$K$2:$K$1156>=’Filtered List’!$A$2,ROW(‘Car Data1’!B$2:B$1156)-ROW(‘Car Data1’!B$2)+1),ROWS(‘Car Data1′!B$2:’Car Data1’!B3))),””)
Help me see what I’m missing!
Note Filtered List!$A$2 = 8
I found the solution to my problem, which wasn’t very obvious, despite it being vaguely mentioned in the instructions.
Apparently it is “Necessary” to use ctrl-shift-enter when entering the formula. in to the cell.
You might want to emphasize this point as it doesn’t break functionally “obviously”, it accepts the formula and “processes” it, It just processes it differently
I had the same problem til I read this. I had no idea that excel have the ctrl-shift-enter option. I’ve since found out that this is to create an “array function”. Which make perfect sense now, when I was troubleshooting my problem looking at the formula I couldn’t see how the IF function would work since if you don’t set it to an array formula the IF function will simple compare the array with a single cell. Of course that is going to spit out FALSE every time. However as an array formula the IF function will effective work as a IF function within a FOR function. so For each cell in array, If this = that then true else false. MAGIC! the amount of times I have used VB in these instances when all I need was ctrl-shift-enter! No words lol
If anyone is interested in categorizing by string (case-insensitive) instead of number here’s my discovery:
Note I use column “F” in my data set and have about 120 rows in my sheet
IFERROR( INDEX(MasterList!A$2:A$120, SMALL( IF(IF( ISERROR( FIND(LOWER($A$2),LOWER(MasterList!$F$2:$F$120))),0,1)=1, ROW(MasterList!A$2:A$120)-ROW(MasterList!A$2)+1),ROWS(MasterList!A$2:MasterList!A1))),””)
This is so well done!!! I used Steve W method for multiple criteria…array multiplication….after struggling and failing with other methods. Thanks for posting this! It took me a while to understand why Rows was being used (until I read the comments). The explanation of how this works is appreciated beyond what my words can convey.
Hi
I wonder if someone could explain what I have done wrong with this spreadsheet.
I am trying to filter out records by year.
AI:1 is the cell in which I input the year as to what records I want to pull from the main table.
Why is it inputting “1” when the year for the first record is 2013.
Thanks for any help – Im going around in complete circles.
Cheers
Steve
It appears to be doing exactly what you want – it is displaying the ID value (1) in the ID column. I’m not sure I understand your question. 🙂
Hi Andrew Roberts,
I tried to make a comment on this page a few weeks ago about how people can do “sub arrays” in Google Sheets since Cathy was having trouble with Google Sheets, but I do not see my comment. Can you publish this comment?
I made an example spreadsheet of “sub arrays” in Google Sheets using the FILTER and QUERY functions:
https://docs.google.com/spreadsheets/d/1jWcPundYSLV_mk2mZoLTNO7wj75HFxlMp4aZak3oaEk/edit#gid=701295229
There are six examples in the spreadsheet.
Dear Isai Alvarado,
Thank you for the link on a sample Google Sheet on using FILTER and QUERY.
Regards,
Alan
In this case I’d prefer advancedfilter.
In Sheet “Filterd List”
in K1: Comb
in K2: ‘>44
This worksheet eventcode in the codemodule of sheet “Filtered List”
Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = "$K$2" Then
With Sheet2.Cells(4, 10)
.CurrentRegion.ClearContents
Sheet1.Cells(1).CurrentRegion.AdvancedFilter 2, Sheet2.Cells(1, 10).CurrentRegion, .Offset
.CurrentRegion.Offset(, 8).ClearContents
.CurrentRegion.Columns(1).ClearContents
End With
End If
End Sub
Advantage: no calculation will take place unless something changes in cell K2 in sheet “filtered List”
I think the formula in A5 might be reduced to:
=IFERROR(INDEX(‘Car Data’!B$1:B$2000;SMALL((‘Car Data’!$H$2:$H$1156>=$A$2)*ROW($2:$1156);SUM(N(‘Car Data’!$H$2:$H$1156<$A$2))+ROW()-10);1);"")
Thanks for the helpful information! My formula appears to be working; however, even though I updated all cells with the formal, all of the rows after the first one are coming up blank. What is weird is that if I click the fx button in one of those empty cells, it’s showing the correct information in the formula result. Have you ever seen that??
Have you included the { } for the array formula? Dragging and copying the data doesn’t necessarily copy the array part of the formula.
Yes, I did. Thanks for the thought though 🙂
My guess would be that your condition in the IF() statement only has one match. Check to make sure it is asking what you think it is asking and that you have multiple rows that meet the criteria. It is designed to show only blanks if there are no more matches.
Can someone help me edit the main example formula so that I can enter a date? (instead of Minimum MPG like in the example)
I have a populated worksheet with the following columns: “Event Start Date”, “Event End Date”, “Event Name”, “Event Type”, “Manager”, “Comments”. In a second worksheet (like in the example), I want to be able to enter a date in cell B1 and the sheet populate rows ONLY within 30 days of that specified date.
I have attached a photo of my excel document for reference. Please let me know if I need to explain in a different way what I am trying to do.
Here’s a link to a working version. I did it in two steps (one for min date and one for max date) because I’m not well versed in array formulas. The results of the first step can simply be hidden. If anyone knows how to do it in one step — e.g. IF(AND(Date<22222,Date<11111)) — that would be superior.
I left the useful data in columns B and H to match the original example, but if you want to use different columns, you'd of course just replace those letters in the formulas.
I guess when I enter a url in the “website” field, maybe I still have to paste in into the body if I want anyone else to be able to see it 😛 http://goo.gl/VUGlDW
I saw a few people on this forum had the question of how to do or/and for multiple criteria. Steve answered correctly for the “and” scenario:
if( (‘Car Data’!$H$2:$H$1156>=’Filtered List’!$A$2) * ( ‘Car Data’!$E$2:$E$1156 = 6 ) ,
The “or” scenario just requires you to use + instead of *
if( (‘Car Data’!$H$2:$H$1156>=’Filtered List’!$A$2) + ( ‘Car Data’!$E$2:$E$1156 = 6 ) ,
Hope that helps
I have done this and it works now but when I save and open the Document again it is only one line and it doesn’t keep it as a list?
I like the solution and Im sure it works fine in earlier versions of Excel, but I think something must have changed (or I have something misconfigured) in Excel 2013:
This part of the function: =IF(‘Car Data’!$H$2:$H$1156>=’Filtered List’!$A$2,ROW(‘Car Data’!C$2:C$1156)-ROW(‘Car Data’!C$2)+1)
doesnt return what I think this function depends on.
It IF clause is looking through an array for instances where value is greater than or equal to specified number and depending on Excel to know the row its on using ROW(‘Car Data’!C$2:C$1156). Interestingly that ROW part always returns 2 on a match. It makes the entire formula fail (because the formula is expecting a real row number to be returned (I think)
Any thoughts about how to fix that?
I just confirmed
If I set a column of values to be ‘Car Data’!$H$2:$H$1156
the column lists the values as they are in the downloaded spreadsheet ‘Car Data’
However if I add that to a ROW function: ROW(‘Car Data’!$H$2:$H$1156), all the values are 2
(= sign is presumed)
Agreed. I don’t see how this formula — ROW(‘Car Data’!C$2:C$1156)-ROW(‘Car Data’!C$2)+1 — can return anything but a static number. The first ROW function gives you “2”, the second ROW function gives you “2”, so you get “2-2+1=1”. What am I missing here?
The ROWS( ) function does not work in an array unlike the ROW ( ) function
This is truly arcane.
One of the downsides of excel is that it is so difficult to edit and document. Be nice if double clicking a cell gave you the box with the nested indents.
How do you document excel in such a way that someone else can figure out what the heck you were doing?
This was a fantastic lesson. I’ve scoured the comments looking for an answer but did not find one: Can you utilize an OR statement as well? For example: What if I would like to pull info for any car that has either 39 MPG City OR 39 MPG HWY…
This is great. One question i have is how do i generate a table that only pulls data from specific columns. I.E. I only want the Make, the Model and the Comb columns in my new tables. The formula as it stand will only generate the data from the columns that are directly beside eachother in the original data set. Would i use the match formula? How would i integrate this?
Two ways to do this that I can think of right off the bat. 1.) You could create the full sub-array as seen in the example and then delete the columns you do not need. 2.) When you drag the formula to the right, drag it only the number of columns you need to see, and then go into the formula bar and adjust for what columns you want to see from the original data set. Ex: The formula to bring info for “#Cyl” is : =IFERROR(INDEX(‘Car Data’!E$2:E$1156,SMALL(IF(‘Car Data’!$H$2:$H$1156=’Filtered List’!$A$2,ROW(‘Car Data’!E$2:E$1156)-ROW(‘Car Data’!E$2)+1),ROWS(‘Car Data’!E$2:’Car Data’!E2))),””)…..but if you change each of the column reference next to ‘Car Data’! to the column you want to see (Comb) then you’ll get the output you want. Try changing it from column E to column H in each respective part of the formula.
I’m really struggling to get this to work on my spreadsheet and I’m wondering if the fact that my data is formatted into a table is having anything to do with it. I need it to be formatted as a table to include new data that is added using a data entry form on another page.
I’m looking to pull out all of the data for a particular project name so that I can chart the changes in the scores over time. We use the month column instead of the date (which is automatically calculated using NOW() when the user enters the data using our data entry form).
Hi,
Great work here and fantastic tips, but I am struggling to put a third “AND” in the formula:
=IFERROR(
INDEX(tbl_Source[Number],SMALL(IF((tbl_Source[Request Date]>=$A$1)*(tbl_Source[Triaged]=$A$4)*(tbl_Source[BusinessArea]=$F$2),ROW(tbl_Source[Number])-ROW(Source!C$2)+1),ROWS(Source!C$2:C2))),””)
Removing “*(tbl_Source[BusinessArea]=$F$2)” works OK, but as soon I enter this third condition then no results.
Any help is very much appreciated
I have it work out…. but that CTRL SHIFT ENTER….. when it doesnt work, be sure that you used CTL+SHFT+ENTER 😉
However, what doesnt work with my configuration is filtering on a date, which is created with “now()” or “now()-1”.
Let’s take cell A1 as cell for the date and used by the filtering. Filling in 4-11-2014 manualy and the filter works; filling “=now()” the cell shows 4-11-2014 (because that is the date i am writing this), but the filter doesnt work!!!
My goal: to filter data from an inputlist based on today and product (i want to have a productlist at the end of every day as hardcopy).
Any suggestions?
The now() function returns a serial date with a decimal portion representing the time of day (i.e. Date + Time.) You may need to use the date() function instead, which returns only the integer portion (i.e. today’s date.)
You may already realize this, but is displayed in the cell isn’t necessarily the raw data that Excel is storing; it depends on the cell’s formatting (which can be changed from the right-click menu.) For instance, Excel may be storing the full Date + Time serial number but displaying just the human-readable Date (or the human-readable Date + Time.)
Is there any one who can help me out understand if there is any possibility I can filter my data based on a formula value in a cell. To explain my data contain resulting numbers based on multiply calculations I-e =ROUND(I6*(100%+$J$3),2), =ROUND(I7,2) and would like to filter the cell based on a criteria to line up cells that only contains part of the formula *(100%+$J$3).
Hi
I hope someone can help me pretty quickly. Please refer to the image I have uploaded. I have attached a sample of the data that will be shown on a separate sheet (lets call it ‘Sheet 2’). The source data is the second table (headed ‘Data’) I have 2 filters that selects from lists on a separate sheet. The first filter is the title of the magazine (or ‘Title’ and a total of the magazine portfolio (‘Portfolio All’. This will sum all the orders by channel (Campaign Channel 2).
The second filter which picks up data from the ‘Campaign Channel Primary2’ field in the data (currently populated with ‘Christmas’ called ‘Campaign Type’) will show all orders from that particular portfolio campaign only (when filtered). Other options to select from there and will be included in the data set are Easter, January Sale, Summer Sale etc.
My question is what formula(s) do I need to populate the table (Headed ‘Order Volume’) by all the channels in the field ‘Campaign Channel 2’ by the 2 filters that are selected (headed ‘All Channels’ in the first table where I want the filtered data to go, so that the data changes to either total volume data by ‘Portfolio All’ (the total) or by a specific magazine (Mag 1, Mag 2 etc), If I can understand the formulas required to capture data from 2 filters in the ‘This Year’ column field in the table, then I will be able to do the rest.
Any help here greatly appreciated. I can then use the filtered data to crates dynamic charts.
Thank you
If you take a look at the data table you will see order volumes for ‘Mag 1’ ‘Mag 2’ etc.
Just a follow up to my post of summing multi filtered data. The 2 filters are at the top of the worksheet I added (sorry by zoom will be required to see the fields).
The first filter is called ‘Title’ This will filter on data for the title of the magazine (i.e. Mag 1, Mag 2 etc), which will show the order volumes by channel (contained in the field ‘Campaign Channel 2).
The second filter is called ‘Campaign Type’. This picks up which portfolio campaign it is (this data is contained in the field ‘Campaign Channel Primary2’)
I just need to understand how the data can be dynamically linked to these filters so that the correct data appears in the table headed ‘Order Volume’. If we can get a formula together to populate the ‘This Year’ heading then I can follow that to populate the others.
Thanks for your help.
Andrew, thanks for the tutorial. this is one of the best, if not THE BEST site for advance excel tutorial. i learn a lot from this and applied it in my excel. thank you sir
Hi,
IFERROR(INDEX(‘RadGridExport-2’!A$2:A$1156,SMALL(IF(EXACT(‘RadGridExport-2′!$E$5,’RadGridExport-2’!$E$2:$E$1156),ROW(‘RadGridExport-2’!A$2:A$1156)-ROW(‘RadGridExport-2’!A$2)+1),ROWS(‘RadGridExport-2′!A$2:’RadGridExport-2’!A2) )),””)
IFERROR(INDEX(‘RadGridExport-2’!A$2:A$1156,SMALL(IF(AND(‘RadGridExport-2’!$E$2:$E$1156>=3.2,EXACT(‘RadGridExport-2′!$E$5,’RadGridExport-2’!$E$2:$E$1156)),ROW(‘RadGridExport-2’!A$2:A$1156)-ROW(‘RadGridExport-2’!A$2)+1),ROWS(‘RadGridExport-2′!A$2:’RadGridExport-2’!A2) )),””)
While I was trying to user the above code its working f9. But when try to add “AND ” formula its not giving the required Value.
I want to use two condition so tried the “AND” formula but facing problem
Can anyone help me with this.
Thanks in Advance !!
This almost works for me…
I’m trying to process data from a large set which is pasted into my ‘raw data’ sheet, based upon a criteria selected from a drop-down on the ‘raw data’ sheet.
So in my ‘sales margin’ sheet, I retrieve the users drop-down selection from ‘raw data’ into cell A1.
I then use the sub-array to find values greater than or equal to whatever A1 is.
The formula only ever finds the first record in the data, but literally the first (not the first that matches the argument), and finds every related piece of data correctly.
This is my formula, in cell A4:
=IFERROR(INDEX(‘Raw Data’!B$3:B$519,SMALL(IF(‘Raw Data’!$I$3:$I$519>=$A$1,ROW(‘Raw Data’!B$3:B$519)-ROW(‘Raw Data’!B$3)+1),ROWS(‘Raw Data’!B$3:’Raw Data’!B3))),””)
The column containing the data by which i want to filter is “I”.
Hope someone can shed some light on this for me!
Hello,
I am hoping to perform a similar calculation as that above, only selecting columns dynamically, rather than rows. In my set up, I have an initial column with time-steps, and then subsequent columns with water level data for individual wells (i.e., each column after the first has data for a single well). There are 100+ wells, so I want to look at them in groups. Above the header row with “Time” and well names, each well is assigned a group name. That group name is what I want to use as the selection criteria for which columns to print in the filtered table. I have been able to apply the following equation to a small dummy table, but when I apply it to my larger dataset, I get an error message. To the best of my understanding, the two equations are written the same way, but any insight would be appreciated.
For the dummy table (this one works; the particular group name is “N”):
=IFERROR(INDEX(Sheet5!$B$2:$E$7,0,SMALL(IF(Sheet5!$B$1:$E$1=”N”,COLUMN(Sheet5!$B2:$E2)-COLUMN(Sheet5!$B2)+1),COLUMNS(Sheet5!$B2:B2))),”error”)
For the real dataset (this one I get an error; group name is “Y”):
=IFERROR(INDEX(Data!$B$6:$JP$1555,0,SMALL(IF(Data!$B$4:$JP$4=”Y”, COLUMN(Data!$B6:$JP6)-COLUMN(Data!$B6)+1),COLUMNS(Data!$B6:B6))),”error”)
Attached is a image of the dummy table.
Hi All
i’m new to this advanced excel sorting and seek the advice from one of the professionals.
I have a similar problem and would be grateful if one could assist.
basically there are two columns 1) Email and 2)Type
the email entries with the criteria containing “.co.za” (south african) address
OR
the entries which have a type from South Africa must be extracted
I thought the functions =if, =vlookup, =and, =right might be used
however not sure in which order.
would be appreciative if one of the excel experts could assist.
thank you
moss (dot) 27 (at) hotmail (dot) com
I had tried this functionality once before using the same formula shown on another website and no luck. I did it your way for 15 minutes and eureka it worked. Thank you, thank you, thank you
I have been trying this for a couple days now. I got it to not throw up errors but it keeps giving me ALL the data from the page instead of the just the ones with matching UPCs.
=IFERROR(INDEX(Club_NbrCM,(SMALL(IF(UPC_CM=UPC_C,ROW(Club_NbrCM)-ROW(‘Current Month’!B$2)+1),ROWS(‘Current Month’!B$2:’Current Month’!B$2)))),” “)
Is my formula. the UPC_CM is a col on my list .. the UPC_C is a field populated by a vlookup based on drop down boxes. Has anyone else used this to build the reports based on drop down box data? Have been reading everything but not getting what I am doing wrong here.
Thanks for the help!
Ok i validated that it does pick up the UPC number when I switch the drop down boxes. What I don’t understand is why it won’t validate it against the one in my database and ONLY return rows that have that UPC number.
I can dumb up the data and send someone a copy of the file but do not know about posting it here as it is job related…. even if it is my own personal challenge to create this.
Look forward to hearing back! Thanks
Ok making progress but still hitting a wall when I try to use * in my if statement to check another field.
Also when copying the formula it continues to bring back the same club over and over instead of putting in blanks where needed.
=IFERROR(INDEX(Club_NbrCM,(SMALL(IF((UPC_CM=UPC_C)*(CM_Code=0),ROW(Club_NbrCM)-ROW(‘Current Month’!B$2)+1),ROWS(‘Current Month’!B$2:’Current Month’!B$2)))),” “)
Errors out with #NAME? but the below brings back the correct data but does not allow me to check more than one thing. I can get by with that, just more manual work to filter a clean page.
=IFERROR(INDEX(Club_NbrCM,(SMALL(IF((UPC_CM=UPC_C),ROW(Club_NbrCM)-ROW(‘Current Month’!B$2)+1),ROWS(‘Current Month’!B$2:’Current Month’!B$2)))),” “)
When pulled down the page though i get the same data in each row instead of giving me a list.
So instead
Club
1
2
3
I get :
Club
1
1
1
HELP please!
I replied above in another related post after reading all of them for a possible answer but see I probably need to reply here to have my issue better noticed. Sorry for double replies in this manner but I need help.
I have successfully used this lesson but am still having trouble with “0” returned for blanks in my master list. Due to date formatting I am using in some columns the 0s could create confusion and I need the blanks. I cannot seem to insert the fix already mentioned by Andrew in an above thread into my formula:
=IFERROR(INDEX(‘2015 work ‘!A$2:A$156,SMALL(IF(‘2015 work ‘!$P$2:$P$156=’Smith’!$B$1,ROW(‘2015 work ‘!A$2:A$156)-ROW(‘2015 work ‘!A$2)+1),ROWS(‘2015 work ‘!A$2:’2015 work ‘!A2))),””)
Please help. Messy formula is a non-issue at this point. Thank you!
This formula does exactly what I am looking for but I am having trouble adapting it to my worksheet and I can’t figure out what I am missing.
I need the rows from the ‘RAW’ sheet where the value from column ‘H’ is greater then 3.
I have 16 other reports that I need to run monthly with similar data extracts and all of them are greater than 2000 rows.
Could someone PRETTY please explain what is causing the ‘invalid’ for the array in the small formula? I’m pulling my hair out.
Ok figured out my issue. I had to remove a $ sign. It kept giving me an error before when i took it out but working today!
Is there any way to have blank row(s) returned at a change in criteria? For example, could the formula return a blank row at every change in Make so that the car makes were effectively grouped together with room to put in subtotals, etc. without having to manually insert lines later? Thanks!
My question is regarding inconsistent rows of data in a single column. For eg. I have a column of Manufacturers with values like “TOYOTA”, “TOYOTO”, “TOY-OTA” etc. but all of them denote the same Manufacturer, TOYOTA. Is it possible to group them together as intended sub-list instead of deleting the special characters or correcting the spelling mistakes. Looking forward to your quick help. Thanks in advance.
Hi,
This is a wonderful site!
I am in a pickle! I want to search my array for two criteria
(1) search my data-set (column “K”) for all values that are less than or equal to a number (e.g., 10) — This I solved, thanks to this is website, with the following equation below.
=IFERROR(INDEX(K$6:K$1156,SMALL(IF($K$6:$K$1156<=$E$4,ROW(K$6:K$1156)-ROW(K$6)+1),ROWS(K$6:K6))),"")
(2) However, I also want to incorporate another criteria that will do a non-specific keyword search in column "Q" of same data-set, e.g., "Products"
WHAT I NEED: a function that will combines criteria outlined in (1) and (2).
Any help will be beyond appreciated. 🙂
Thank you,
Rez
A million thanks fro the above formulas and all detailes info – it was my life saver 😉
This formula has worked great in my file except I have 1 row that is repeating twice. Any help?
I figured it out but I don’t know the solution yet. My filter list has S column, O column and D column that have amounts. If there is an amount in S & O or S & D or S, O & D it pulls it twice, I only want it to pull once.
Never mind I figured it out. Thanks!
Glad you solved your problem! Care to share the solution with the rest of us?
This was super helpful. thank you. However would be great if you could show me how we do this for 2 or more criteria.
You can have multiple criteria by using …SMALL(IF((Range 1=Criteria 1)*(Range 2=Criteria 2)… which will match all criteria the same way an AND function would. You can also use …SMALL(IF((Range 1=Criteria 1)+(Range 2=Criteria 2)… which will match any criteria the way an OR function would. You can get creative with multiplying and adding groups of criteria in parentheses to further narrow the list of results.
Hi guys,
I really want to tell you a little funny story on this tutorial.
I live in Belgium and this tutorial really answered a very specific question, however, following the step by step guide resulted in an error…
For some reason even with an exact copy of the structure I still received an error message… You want to know the reason why?
My Excel runs in Dutch so he didn’t recognized all english functions 🙂 LOL
Dear Mr. Roberts,
this is by FAR the single most helpful excel tutorial on the web, easily rendering half the amount of macros in my organization obsolete and unnecessary. Thank you so much for this!
Hello,
I’ve gotten one of these dynamic arrays to work on one of my spreadsheets, using a number of if filters on arrays using the suggestions above. It works beautifully.
On my second spreadsheet, I can’t for the life of me get it to work. My formula is currently:
{=IFERROR(
INDEX(
‘Full List’!$A$3:$A$165,
SMALL(
IF(
‘Full List’!$AM$3:$AM$165>=$C$2,
ROW(‘Full List’!$A$3:$A$165)-ROW(‘Full List’!$A$3)+1),
ROWS(‘Full List’!$A$3:’Full List’!A3)
)
),
“still no”)
}
The problem *seems* to be at the point of the array of values ‘Full List’!$AM$3:$AM$165 being checked by the IF statement. The only thing I can think of is that those values are the final product of a series of complex equations. When I change it to check a column that is just entered values, then it works fine.
What the heck am I missing here?!
Issue resolved – the problem was that one of those many formulas was being drawn from a #NT/FND error, causing a #VALUE error, which then if there were *any* checks being made against those #VALUE errors, then it was returning an error to the array / index.
Phew, that was a bit of a pain in the arse to fix, but it worked…
Hello everybody! I would really really appreciate your help because I am losing my mind already:(
My array formula works pretty much fine until I try to add new raws to the database from which the formula gets its data. Every time I am expanding it, the formula starts to return blanks, it is as if nothing is being calculated! I have tried to redo the formula , i have checked all possible format errors, I am simply desperate!
My formula : IFERROR(INDEX(‘Stock Mgmt KPIs sem tabela’!$A$3:$A$1000; SMALL(IF(‘Stock Mgmt KPIs sem tabela’!$C$3:$C$1000=$A$3;ROW(‘Stock Mgmt KPIs sem tabela’!$A$3:$A$1000)-ROW(‘Stock Mgmt KPIs sem tabela’!$A$3)+1);ROWS(‘Stock Mgmt KPIs sem tabela’!$A$3:A3)));””)
Thank you!!
This has helped me TREMENDOUSLY!!!! Thank you for putting this together. I had been working on this issue for weeks before stumbling upon your page. After reviewing your steps, I solved my problem in less than 2 hours.
Thanks again!!!
Great Article ! Efficient solution, and well explained. Thanks a lot for putting this together.
It took me some time to figure it all out, because it’s far from being simple, but I tested the formula by breaking it down, and I finally got it.
Great presentation.
Question 1: Can this filtered list sub array be used to reference Excel Tables using structured reference or named ranges? All of my data is in Table format and I would really like to use the structure or named references for readability. I can’t seem to get those references to work; a #NUM error is returned in the SMALL function.
Question 2: How was the duplicate output problem solved? (see Regina Feb. 25, 2015 post) I too, recieve duplicate values as well as leading blank rows.
Absolutely brilliant, just what we were looking for. Thank you so much.
No matter what I do, I am unable to re-create the results shown here.
I downloaded the excel file example from here. When I open up the file the result is fine Ford, Honda, etc.
However in cell A5, if I hit F2 (selecting the column A array) and then Ctrl+Shift+Enter, I just get Ford repeated all the way down to A45!. Without ANY modification to the formula.
I have tried replicating the formula in excel 2003 (without the iferror) I get the same repeated result. I have also tried a different formula that is supposed to give the same result as above and that too just gives me the same data repeated over and over.
I thought maybe its a setting on my PC, so I tried it on someone else’s PC and I still get same repeating result. Maybe both PCs have some setting messing this up?
HELP???
Ok I am completely lost – I have tried one another formula (a total of 3 so far) and this spreadsheet on 4 different PCs still gives the same repeated result – as far as I can make out, the Small function is always returning the same value…
Anyone seen something like this? I could not attach the spreadsheet with the 3 different formula that achieve the same result (all not working) so here is a jpg.
Help!??
fabulous – works like a dream – have adapted it to several running cost scenarios
many thanks!
Great explanation and got it to work which is brilliant. My next step is that the search I need to carry out is just 4 digits of a 16 digit code. My initial thoughts were to replace the if function with match() but it doesn’t seem to work, presumably because match is just returning the same index location?
This article is great!
I have a question though…
What if my criteria is not a number, but a text? How would you change the formula for that? Would that be a completely different scenario? How would you do that with formula? Any help would be greatly appreciated! 🙂
GREAT!!!
Tonight you’re my hero!!! :o)
I’ve been banging my head against the wall here and I think its because the column I’m searching has a list of values vs just 1 value. My spread sheet has shops and in column A there is a list of all the zip codes within a 10mi radius. I want to put in a zip code and return a list of shops that ‘service’ that zip. The zips are just entered as text 12345, 12346, 12347, 12348……
Can you help me out?
Excellent!! Thanks.
Thanks so much for this really thorough work through. I’m having problems, though – I can make the formula work for the first line in my table (selecting and displaying the expected first line of my output table), but when I try to drag it down it won’t display and further lines – is this something to do with dragging an array file? When I look at the empty cells, they seem to have the correct formula in them.
Thanks so much for explaining this, It is well set out!
Have a wonderful day
In the last part of the formula, where it says “ROWS(‘Car Data’!I$2:’Car Data’!I3)))”, I am not able to get this formula to fill down the page properly. I3 repeats instead of incrementing I3, I4, I5, etc., down the worksheet. This is causing the formula to return the same value all the way down. I’m holding CTRL+SHIFT+ENTER, and it’s turning into an array formula, but even though there is no $ in front of the 3, the cell reference is holding static. Even when I copy/paste your formula to the next row I can’t get it to work.
Put a $ between I3. Should be ROWS(‘Car Data’!I$2:’Car Data’!I$3)
Thank you so much for such an elegant solution!!!
I guess I fixed that problem..!
I wasn’t creating an array formula.
the only trouble is that when i enter an out entry in the Astra Stock it obviously deletes the formulas for that row. therefore the next entry which should pull across from the other table doesn’t.
Basically this part of the formula ‘ROWS(D$7:D9)’, e.g. the ‘D9’ needs to be created dynamically….
Is this possible. for example:
all the entries useing the formula have a reason of “Astra Stock”, so i could you use a count on these to determine what the next number is..
so if the first row is D7, then the second row has a manual row so the formula has been deleted, the third row which would normally be D9 has got to return D8,. so the count returns 1 so by adding 7 to whatever the count returns i can have the right number.
the only thing is … is it possible to put that number back into the formula to create ‘D8’??
Is it possible to get this to work with an array across multiple worksheets? I’ve got it working fine when the data is only being extracted from one page.
Thanks
Hi
1) I don’t see the sample file – is it still available?
2) I haven’t tried to figure this all out yet – I’m not an expert with Excel. I wanted to filter by multiple criteria, I’m just using the ‘advanced filter’ – is that essentially what you are doing here, but with formulas? And if so, why would you not just use advanced filter?
Thx
Great solution! Thank you very much! I got this to work for a single data source. How can I use this if I have multiple lists on different sheets? The lists all have the same columns, but the lists are from different departments. I would like to pull the filtered data from each list and only display on one output list.
Thank you in advance!
How would you make this formula work to only give you 1 model (no duplicates of the different models)?
I can’t get it to work. As soon as I change the value we are searching for (ie 45), it just returns random information from the list and not even from the same lines. It worked the first time fine but as soon as I change the criteria it begins giving me the wrong info.
thanks for any advice
A possible mistake (which I experienced before) is to accidentally edit a cell containing an array formula by putting one’s cell pointer in it, pressing F2 (to start editing it), then changing one’s mind about editing it, but just pressing Return instead of pressing Ctrl-Shift-Return upon exit.
There may be no error message if the formula still makes sense to Excel, only that it’s no longer an array formula but a regular formula, causing unpredictable results.
Verify that each cell in the Filtered List still contains array formulas by making sure the formula is preceded by “{” and ended by “}”. Or just verify the first row of array formulas, then copy down all the verified formulas.
I have been struggling to understand array and index formulae for a long time, and this is the only explanation I’ve found that actually makes sense to me! I think I now understand it, thank you!
Thank you very much! I was looking for this for a very long time!
I have a helper column next to my data that is either true or false depending on the option i select in the second sheet. I would like to move all of the rows that have a “true” value to be filtered in the second sheet.
Would =small still be an appropriate function to use? I was thinking of match and find, but I just can’t really get my head around this whole row and rows thing.
Any help would be greatly appreciated!
This is a great tutorial! Does this formula work if the criterion is a letter instead of a number? I would think it would, but for some reason mine is not working properly. I am getting values returned (ie not errors), but they are not correct values.
I have a column that has an A (for Active), P (for pending) and T (for tracking) for a list of projects my company is pursuing (ie biz dev). I want to have a sheet that pulls all of the projects that are As and Ps and i want to bring that into a separate sheet with only a subset of the output columns that are in the original data (hence why the basic excel filter alone is not good enough). I have double checked the formula and it appears to be exactly as you have it in your example. The only difference appears to be that my criterion is a letter, not a number.
Any thoughts on where i might be going wrong would be greatly appreciated.
Best,
Dan
thank you so much, it’s very helpful..
Thanks, this is a great solution.
Is there a way to make the result grow and shrink automatically as the criteria changes the number of matching records like a queried table?
An thought on to implement this?
Thanks in advance.
Thank you. This is such a great example and very well-explained.
I have been looking for this kind of solution for a long, long time (one that does not require macros or programming).
Just wanted to say that this is a phenomenal tutorial. You saved my bacon, and you’re awesome.
This is really useful. However, it seems to rely on the output rows being aligned with the input rows. (That is, both the source data and result need to start on the same row number.)
Can anyone enlighten me as to why, and/or how to work around this?
This is a great help – but will this work when the criteria I’m trying to filter by (So the A2 value in your example) is non-numeric, ie a name such as John Smith?
Looks exactly what I’m looking for, but can’t find that green Excel button to download the file
Hi Tom! Since this article was written, the embeded excel interface has changed. It is now a download button that looks like a piece of paper with an arrow coming out of it. I’ve updated the article text to reflect the change.
If i wanted to duplicate this formula but so that it could pull from three arrays arranged on a spreadsheet next to each other would this be possible?
So rather than having just manufacturer in B, it is also in lets say C and BA – with the same following columns. However, when I pull the filtered list, I would want the names makes from B to be followed by the makes in C when their are no more matches and so on with BA?
Hope that makes sense
This is a lifesaver – thank you so much! I’ve been trying to come up with a solution like this for some time now that didn’t involve macros or VB (network/firewall restrictions for some users) and this worked perfectly once I set up a few helper “True/False” columns. It was curious to me that I couldn’t put more than one parameter in the formula itself, but maybe it was just me or maybe I’ll figure it out later. Thanks again!
I used this setup for the sub array to list data required, but the issue I’m having is that it only returns one entry matching the criteria instead of the list. What am I doing wrong?
Hi Peter!
Most likely, you are not entering the formula as an array. Press
Ctrl+Shift+Enterinstead of justEnterwhen completing the formula.I have this same issue and am pressing Ctrl+Shift+Enter instead of just Enter when completing the formula. I receive the notice “You can’t change part of an array.”
Well written article, I have both fingers crossed that it works.
Hi – Great tut. Allows me to create a master list and sub-lists that update when new items are added. It’s absolute gold! (Unfortunately, I’ll probably have a hard time convincing anyone in my industry (architecture) that its more useful than confusing lol…)
Anyway, my question is if there a way to maintain cell formatting (fill color, etc) from the original list when using sub-arrays?
Thanks
Andrew – great write-up on a useful function. Thank you.
I’m attempting to use this with an OR operator within the IF statement as a means of selecting two different specific text strings from the criteria array. It seems to break the function. Any ideas as to why that might happen?
Thank you for this. It worked perfectly!
I’m trying to incorporate this into a spreadsheet and I have followed the instructions. The first line returns the expected data but subsequent lines error “#NUM!” stepping through the function it tells me the return data but then errors out at the end something to do with the “SMALL” function and the associated array.
Formula needs to be entered as array formula by pressing CTRL+SHIFT+ENTER rather than just ENTER.
Thank you, it’s an incredibly versatile formula. I didn’t need this specific example as my case needs were different. But it’s incredibly easy to adapt it towards something I’ve spent a day searching a way to do.
Hey there, great explanation and thank you for providing the data and example sheet.
In Excel 2016, I’ve created a “Car Data” sheet as well as a “Filtered List” sheet. I’ve copied all of the Car Data as it is listed, and copied the MPG requirement on “Filtered List” to cell A2.
I copied the formula as written in your example Cell A5, selected quite a few cells in my Column A (starting at Cell A5), pasted the code, and hit Ctrl-Shift-Enter. I get a whole column of Fords. Has anyone else had this issue in a newer version of Excel? I’m wondering what I could be doing differently, since I’ve copied everything I can and matched it as well as I can to what is given here.
Can this formula be used in a table?
IT WORKED Beautifully!!!!
Will have to spend some time to understand the logic, but IT WORKED Beautifully!!!!
Thanks a lot!!!!!!!!!
Incredibly frustrating flicking through multiple pages and multiple loads
I’ve replicated the formula where I replace the number in ‘Filtered List’!$A$2′ with a string so that it looks for “2-Wheel Drive, Front” and changed ‘Car Data’!$H$2:$H$1156 to Car Data’!$L$2:$L$1156
That works fine. But now i want to return all rows which have “2-wheel drive” but either ‘front’ or ‘back’ so I know I need to search for a substring instead of the exact match.
How do i do it?
Sir, I can’t thank you enough! Not an Excel guy, but a math guy, and your explanation of the formulas was easy to follow and adapt for my own use.
Thank you!