Nested filter

Copper Contributor

Can someone please tell me why any of these formulas arent working? They seem to be correct. 

 

Thank you: 

 

=FILTER(FILTER('Version Ctrl'!$B$2:$B$27,'Version Ctrl'!$B$2:$B$27<>""),'Version Ctrl'!$B$2:$B$27<=A3)

 

=FILTER('Version Ctrl'!$B$2:$B$27,AND('Version Ctrl'!$B$2:$B$27<=A3,'Version Ctrl'!$B$2:$B$27<>""))

1 Reply

@mantwi2 

 

Not for sure, since I've not got access to your spreadsheet/workbook. You might try removing the absolute references.

 

=FILTER('Version Ctrl'!$B$2:$B$27,AND('Version Ctrl'!$B$2:$B$27<=A3,'Version Ctrl'!$B$2:$B$27<>""))

That isn't the way to combine two criteria in the FILTER function. Instead, the criteria would be combined with an asterisk between them

....('Version Ctrl'!$B$2:$B$27<=A3)*('Version Ctrl'!$B$2:$B$27<>"")...

 

Here are two good resources to learn FILTER:

https://www.youtube.com/watch?v=9I9DtFOVPIg

and

https://exceljet.net/excel-functions/excel-filter-function

 

If you're not able to resolve it, see if you can come back and post either the actual file OR a mockup, just ensuring no confidential data is in it. If you can't post it here, use OneDrive or GoogleDrive and give us a link.

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...