UDF as an alternative for the new FILTER function built-in for the Excel 365

[omairhe]

Hey Excel Gurus,

How are you guys doing today? I need a big favor from you. I am using older version of Ms Excel and in it the newer FILTER function that works by default in Office 365 is missing. I was hoping if anyone can make a UDF that could work exactly same way as the original.

Thanks a lot and will appreciate..

 

[omairhe]

Looks like you are just dealing with a column of values so would this also work for you?
I've just done it on one sheet but you should be able to adapt to two if it meets your need.

Function Fltr(col As Range, FilterFor As String)
  Fltr = Filter(Application.Transpose(col), FilterFor, True, vbTextCompare)
End Function

omairhe 2020-02-24 1.xlsm
	A	B	C	D	E
1	Substitue		sub		Substitue
2	Intersub				Intersub
3	manly				substance
4	substance
5
Sheet2

Cell Formulas
Range	Formula
E1:E5	E1	=IFERROR(INDEX(Fltr(A$1:A$4,C$1),ROWS(E$1:E1)),"")

Thank you, it fulfills my requirement..

 

[Peter_SSs]

You're welcome.

BTW, you could also use standard worksheet functions to do the job without needing vba.

Depending on your Excel version [I suggest that you update your Account details (click your user name at the top right of the forum) so helpers know what Excel version(s) & platform(s) you are using as the best solution often varies by version] you could use something like this.

omairhe 2020-02-24 1.xlsm
	A	B	C	D	E
1	Substitue		sub		Substitue
2	Intersub				Intersub
3	manly				substance
4	substance
5
Sheet3

Cell Formulas
Range	Formula
E1:E5	E1	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$1:A$4)/ISNUMBER(SEARCH(C$1,A$1:A$4)),ROWS(E$1:E1))),"")