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.
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!