Forum Discussion
Excel Formula
Do you have the FILTER function available? It's in the latest updates to Excel 365. If so, you could try filtering with two criteria
- Item #=item you need
- date = MAX(Date)
Without an actual example from you of your actual spreadsheets, it's hard to be more specific.
mathetes Thanks buddy, Sample data attached
If without dynamic arrays that could be
=INDEX(Sheet1!$B:$B, MATCH(1, INDEX( (Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))= MAX(Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))))* (Sheet1!$C$1:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$A:$A))=$A2), 0), 0) )SergeiBaklan Thanks Buddy, could you please advice logic behind such formula construct, unable to get the result. (Index, Match, Index, Index, Counta, Max)
If FILTER() is available in your version of Excel that's definitely better to use it.
If not, in any case it's better to use Excel structured tables for the source data.
If not (you don't want to invest some time to learn about structured tables or you are on something like Excel 2000), when it's better to use dynamic ranges.
In the formula the part as
(Sheet1!$A$1:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))returns only actual data range, not million of rows if you use Sheet1!A:A instead. Alternatively you may use something like Sheet1!$A$2:$A$19, but that's not a good ide from maintenance point of view - you have to change the formula each time the range is changed.
With that main formula is
=INDEX(Sheet1!$B:$B, <row number where condition is met>)other words we take from column B value in row #, the rest is to calculate which row number to take.
Next step is to find such row number, we use MATCH() for that
MATCH(1, INDEX( (<dateRange>=MAX(<dateRange>)* (<IDrange>=<ID>), 0) , 0)Criteria within as
(<dateRange>=MAX(<dateRange>)* (<IDrange>=<ID>)returns sets of 1 (TRUE) and 0 (FALSE) if criteria met or not met. With MATCH(1, ... we find first one for which criteria is met.
We may avoid of using INDEX() here but when we shall use array function entered with CTRL+SHIFT+ENTER. If with INDEX() it natively returns an array to MATCH() and we may work with regular formula.
Again, above is legacy style which could be used if you don't have or don't want to use modern Excel functionality. Some of "modern" functionality as tables is available for at least 10 years, some as FILTER() appeared for everyone on 365 about an year ago or so.
Attached shows how FILTER would work in that small example file. You will no doubt need to extend or modify the absolute and relative references in a more fleshed out spreadsheet.
Here's a YouTube video that Microsoft put out to explain these dynamic range functions. It will help you more fully understand how to use FILTER and some of the other associated functions. https://www.youtube.com/watch?v=9I9DtFOVPIg
mathetes This worked really well! Thanks for your work on this! This conversation caught my eye and I can see I will be using this example for things I work on.
This was a helpful article as well. (I didn't understand filtering on multiple items at first.)
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759?ui=en-us&rs=en-us&ad=us#:~:text=Syntax:%20FILTER(array,%20include,%20[if_empty])%20Example:%20=FILTER(A5:D20,%20(C5:C20=H1)*(A5:A20=H2)%20Description:,data%20based%20on%20criteria%20you%20define.%20See%20More
I converted the range to a table so as to expand the array as it grew and make for easier reading in the formula.
That FILTER function is very new and its applications are many and varied, and exciting in their potential. Enjoy!!
I've used it, along with UNIQUE and SORT to create cascading data validation lists as well. The attached example only scratches the surface in what's possible there.
