Forum Discussion
Using Filter will not allow me to expand the array of multiple conditions (as it should).
Thanks.
In D5:
=IFERROR(INDEX(Workings!$I$1:$FC$1, MATCH(TRUE, (INDEX(Workings!$I$2:$FC$1000, MATCH($C5, Workings!$C$2:$C$1000, 0), 0)=D$4), 0)), "")Increase the last row number 1000 if you have more than 1000 rows of data on the Workings sheet.
Fill down and to the right.
Thank you Hans Vogelaar.
Here is the link: and you should be able to download it from there:
Thanks.
In D5:
=IFERROR(INDEX(Workings!$I$1:$FC$1, MATCH(TRUE, (INDEX(Workings!$I$2:$FC$1000, MATCH($C5, Workings!$C$2:$C$1000, 0), 0)=D$4), 0)), "")Increase the last row number 1000 if you have more than 1000 rows of data on the Workings sheet.
Fill down and to the right.
Thanks for this.
The issue is that it works partly in my case, because I have more than one occurrences sometimes on the same date.
For example Vehicle 6 on Feb 19 2029 has 10A, and 10C. Using Filter I am showing the 10C, which is a superior service than 10A.The Index and Match will only show me the first match.
If there is nothing I can do with the Filter option, then the simplest way would be to use your formula but to put the 10Cs columns before the 10As in the Workings spreadsheet.
Is the Filter option not viable?
Thanks again.
Perhaps this?
=INDEX(SORT(FILTER(TRANSPOSE(Workings!$I$1:$FC$1),TRANSPOSE(INDEX(Workings!$I$2:$FC$1000, MATCH($C5, Workings!$C$2:$C$1000, 0), 0))=D$4,""), , -1), 1)
- Thanks Hans,
This formula will still return one item, because while you are using filter (which is a spill formula), you are using index & match nested inside it, which gives the filter function only one cell, rather than array.
However, I swapped the columns order, and used your previous formula with Index&match, and now it all works. I am sure the Filter function would have needed a slight change, but unfortunately I can't find it.
Thank you for your prompt response on this.
