Using Filter will not allow me to expand the array of multiple conditions (as it should).

[GalinZ]

Hi All,

I am trying to make a very specific Service Planner for vehicles.

I have a range in Spreadsheet "Workings" that looks like that (Screenshot below). In Row 1 from Column I up to Column FC each cell has written either 10M, 10A or 10C in it. The dates are populated for each Vehicle from row 2 onward.

In another spreadsheet "Planner" I have the dates in a year in row 4 and trying to pull the 10M, 10A or 10C from the first sheet by using Filter that correspond to each vehicle (screenshot below). Using Filter is requirement because there are sometime more than one occurrences for the same date.

The highlighted formula above is where I need help.

FILTER(TRANSPOSE(Workings!$I$1:$FC$1), (TRANSPOSE(Workings!$I3:$FC3)=Planner!K$4)*(TRANSPOSE(Workings!$C3:$C3)=Planner!$C5), "")

This is all working perfectly, but only because I am giving the filter exact rows to include (Row 3).

The desired outcome is that I should be able to change the Vehicle registration in Cell C5 and it should be searching for it in Workings spreadsheet.

I tried expanding the conditions array it but it returns an error Value. - FILTER(TRANSPOSE(Workings!$I$1:$FC$1), (TRANSPOSE(Workings!$I2:$FC10)=Planner!K$4)*(TRANSPOSE(Workings!$C2:$C10)=Planner!$C5), "")

Link to example workbook:  https://www.dropbox.com/scl/fi/761hz3b968cu82czazl0p/Service-Planner-Example.xlsx?rlkey=6t64d19osjuu...

Note: In the screenshot the Filter function is wrapped in Sort and Textjoin and Left as I want the results to be showing in only one cell, and then showing only the first result. But this is irrelevant for my question. It is the Filter function that I need to make to work.

This has been bugging me trying to solve it.

Please help.

[#2]

@GalinZ 

Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

[#3]

Thank you Hans Vogelaar.

Here is the link: and you should be able to download it from there:

https://www.dropbox.com/scl/fi/761hz3b968cu82czazl0p/Service-Planner-Example.xlsx?rlkey=6t64d19osjuu...

[#4 · Accepted solution]

@GalinZ 

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.

[#5]

@HansVogelaar 

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.

[#6]

@GalinZ 

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)

[#7]

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.

[#8]

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