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.
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?
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)
