Alternative to "FILTER" Func with Multiple Criteria for Office 2019

[GijoeBlack]

Greetings All

I am out of ideas on how to implement a "FILTER" Func with Multiple Criteria for Office 2019 which does not support the function while Office 365 does. I have following example setup on O365 Excel and works like a charm.

I have (2) Workbooks - Workbook A [DCI Invoice.xlsx], Workbook B [DCI-TEST.xlsx]. The FILTER func is being used within Workbook A to bring in certain Columns/Rows from Workbook B so long as it meet the (4) conditions where $F$6 is within Workbook A. See below:

=FILTER('[DCI-TEST.xlsx]DCI Invoices'!$B:$F,('[DCI-TEST.xlsx]DCI Invoices'!$A:$A=$F$6)*('[DCI-TEST.xlsx]DCI Invoices'!$Z:$Z>=1)*('[DCI-TEST.xlsx]DCI Invoices'!$H:$H=0)*('[DCI-TEST.xlsx]DCI Invoices'!$G:$G<>0),"NOT FOUND")

Any advice / help is greatly appreciated.

NOTE: I have utilized INDEX, Match, OFFSET for some other areas but can't figure out how to include the above (4) Criteria.

 

[Eric W]

Here's one way:

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Col B	Col C	Col D						Row	Col B	Col C	Col D
2		B1	C1	D1		1	A		1	8	B7	C7	D7
3		B2	C2	D2		2	A		Z	10	B9	C9	D9
4		B3	C3	D3		1	A
5		B4	C4	D4		2	A
6		B5	C5	D5		1	A
7		B6	C6	D6		2	Z
8		B7	C7	D7		1	Z
9		B8	C8	D8		2	Z
10		B9	C9	D9		1	Z
11		B10	C10	D10		2	Z
12
Sheet16

Cell Formulas
Range	Formula
J2:J11	J2	=IFERROR(AGGREGATE(15,6,ROW(Sheet16!$B$2:$B$11)/(Sheet16!$F$2:$F$11=I$2)/(Sheet16!$G$2:$G$11=I$3),ROWS(J$2:J2)),"")
K2:M11	K2	=IF($J2="","",INDEX(Sheet16!B:B,$J2))

This is simplified (2 conditions), and all on one sheet, but you should be able to see how to adapt to your situation. I put sheet names on the references that go to the other workbook.

 

[GijoeBlack]

@Eric W Thank you for looking into this. I will give it a try and update or if I have any questions. Having said that, could you think of using Index,Match, Offset somehow?

Also, I assume (Sheet16!$F$2:$F$11=I$2)/(Sheet16!$G$2:$G$11=I$3) are the (2) conditions that you are referring to. Are you somehow able to show how will I use the conditions in question that I posed? This will be very helpful.

Thanks again.

 

[Eric W]

Having said that, could you think of using Index,Match, Offset somehow?

Maybe, but the way I showed you is the best way I know. I don't think it would be helpful to spend time devising another way. Also, OFFSET is a volatile function, meaning it calculates every time the sheet calculates. This can slow down your sheet and should be avoided if possible.

Also, I assume (Sheet16!$F$2:$F$11=I$2)/(Sheet16!$G$2:$G$11=I$3) are the (2) conditions that you are referring to. Are you somehow able to show how will I use the conditions in question that I posed? This will be very helpful.

I'm not planning on creating a workbook with your names, I'd hoped you'd be able to adapt what I showed you. Here's how I think it would look, but this is not tested and might have a typo in it:

=IFERROR(AGGREGATE(15,6,ROW('[DCI-TEST.xlsx]DCI Invoices'!$B$1:$B$10000)/('[DCI-TEST.xlsx]DCI Invoices'!$A$1:$A$10000=$F$6)/('[DCI-TEST.xlsx]DCI Invoices'!$Z$1:$Z$10000>=1)/('[DCI-TEST.xlsx]DCI Invoices'!$H$1:$H$10000=0)/('[DCI-TEST.xlsx]DCI Invoices'!$G$1:$G$10000<>0),ROWS(J$2:J2)),"")

This assumes you're putting it in cell J2. I also used a range of $B$1:$B$10000 (and for the other ranges) for performance reasons. Using the whole column would probably slow down your sheet noticeably. Pick a row that's well below the bottom of your data. I don't know a better way using older functions, that's why FILTER was invented. As before, this formula returns the row of the matching row, and the INDEX formula returns the individual column values.

 

[GijoeBlack]

Hello @Eric W

Appreciate and noted your thoughts on INDEX,MATCH, OFFSET.

Thank you again for sharing the scenario using the original conditions that I will be able to utilize within the workbook. I will run some test scenarios and let you know how it goes.

Thanks so much ..

 

[GijoeBlack]

Unfortunately, keep on getting "There's a problem with this formula" right at "$B$10000)" referring to "ROW[reference]" though I have got all the conditions in place with entire formula [=IFERROR(AGGREGATE(15,6,ROW('[DCI-TEST.xlsx]DCI Invoices'!$B$1:$B$10000)/('[DCI-TEST.xlsx]DCI Invoices'!$A$1:$A$10000=$F$6)/('[DCI-TEST.xlsx]DCI Invoices'!$Z$1:$Z$10000>=1)/('[DCI-TEST.xlsx]DCI Invoices'!$H$1:$H$10000=0)/('[DCI-TEST.xlsx]DCI Invoices'!$G$1:$G$10000<>0),ROWS(H$37:H37)),"")].

Thoughts...

NVM - I think I found the problem. Gonna keep testing. Ty again.

 

[GijoeBlack]

@Eric W All the test resulted into success. Thank you for your help and direction on this.

Cheers

 

[Eric W]

I'm glad you got it working! I tried your formula, and couldn't find any issues, so I'm glad you stuck with it. Thanks for the update.