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.
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.GalinZ Here's my two cents: the XLOOKUP function can be used to return the applicable row from the Workings sheet based on the REG No. in column C. For example, the following formula will return the range Workings!I2:FC2 for "Vehicle 2":
=XLOOKUP(Planner!$C5, Workings!$C$2:$C$9, Workings!$I$2:$FC$9)Next, there is no reason to TRANSPOSE each range on the Workings sheet, as the FILTER function is capable of filtering horizontally by columns. The following formula will return both "10A" and "10C" in a 1 row x 2 column array for "Vehicle 6" on 02/19/2029:
=FILTER(Workings!$I$1:$FC$1, XLOOKUP($C9, Workings!$C$2:$C$9, Workings!$I$2:$FC$9, "")=K$4, "")The SORT function is also capable of sorting by column, if you set the optional [by_col] argument to TRUE or 1. And, if you only want the first result to be returned after sorting, the LEFT function with TEXTJOIN can be replaced with a single TAKE function as follows:
=TAKE(SORT(FILTER(Workings!$I$1:$FC$1, XLOOKUP($C9, Workings!$C$2:$C$9, Workings!$I$2:$FC$9, "")=K$4, ""),, -1, 1),, 1)Let me know if you have any follow-up questions. Cheers!
