Filter Alternate in Excel 2016

[deepakch]

I have a sheet which has FILTER Function which filters the data based on condition and runs fine on Office 365 BUT it needs to run on Excel 2016.

=FILTER(Sheet!A:A,Sheet!BD:BD="non-BDO")

How can i get it converted so that it returns an array to function.

 

[myall_blues]

See below. You will need to add the relevant sheet reference when the formula is on another sheet.
Note that because it is not a dynamic array like FILTER, you need to copy the formula down to each row. For that reason it would be a good idea to limit the data extents in the formula (i.e. use BD1:BD?? rather than BD:BD).

Book1
	A	B	C	D																																																			BC	BD
1	A		A																																																					non-BDO
2	2		55																																																					x
3	C		None																																																					x
4	4		None																																																					x
5	55		None																																																					non-BDO
Sheet

Cell Formulas
Range	Formula
C1:C5	C1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$1:$A$5)/($BD$1:$BD$5="non-BDO"),ROWS(A$1:A1))),"None")