Filter Function Alternate for Excel 2016

Question

Hi,

I used Filter function and it works perfectly fine, however, I need to share this file with Excel 2016 in which it's not supported.

Appreciate, if anyone could please help me with a formula which works in Excel 2016.

=FILTER(Data,ISNUMBER(SEARCH($A$7,Data[Server Role]&Data[Hostname]&Data[IP Address]))=TRUE,"Not match found")

Many thanks in advance.

Mohammed

Answer 1

Dear respected Mohammed,

Good morning! Thank you for publishing in Microsoft Community. We are happy to help you.

According to the description, I understand you want to use the Filter formula in Excel 2016 but we are sorry to convey that it is currently not possible to do. 

However, you may have a check with a workaround that community MVPs shared before and see if it can work on your side. See: Filter Function availability in Excel 2016 - Microsoft Community

Meanwhile, we will keep this thread open and welcome Excel MVPs and other community members to share their ideas for you. Appreciate your patience and understanding and thank you for your time and cooperation.

Sincerely

Mia | Microsoft Moderator

Answer 2

let your raw data in a1:b5

c1=arraytotext("a1:b5",1) & ";select f01 from sht where f02 like '%" & $a7&"%'")

c3=encodeurl(c1)

c4=WEBSERVICE("https://e.anyoupin.cn/eh3/?sql~cli_tb~sht~" & c3)

Answer 3

Excel 365 with Power Query.

Alternate for Filter()

Compatible with 2016.

https://www.mediafire.com/file_premium/g6kaghdn7adze4d/01_14_24.xlsx/file

https://www.mediafire.com/file_premium/ke4mvqd4q3lr916/01_14_24.pdf/file

Answer 4

VBA:

Function FILTER(ByVal shu_zu, ByVal bao_kuo, Optional ByRef kong_zhi) 'kong_zhi = CVErr(2050)【lao_ban_benOffiehuoWPSbu_jian_rong_ba_ke_xuan_can_shu_she_zhi_wei_cuo_wu_zhi。】

'mei\_ge\_can\_shu\_dou\_yun\_xu\_chuan\_ru（1ge\_zi\_fu\_chuan|1ge\_dan\_yuan\_ge\_qu\_yu|1-2wei\_shu\_zu），gen\_ju\_di\_er\_can\_shu\_lai\_shu\_chu，jie\_guo\_yun\_xu\_shi1ge\_zi\_fu\_chuan\_huo\_yi\_wei\_shu\_zu\_huo\_er\_wei\_shu\_zu。（zan\_bu\_zhi\_chi\_shu\_chu≥3wei\_de\_shu\_zu，qing\_yuan\_liang\_wo\_tai\_lan）

On Error Resume Next

Dim yi\_wei\_xia\_biao As Long, yi\_wei\_shang\_biao As Long, er\_wei\_xia\_biao As Long, er\_wei\_shang\_biao As Long, R1 As Long, C1 As Long, ji\_shu As Long, X As Long, bian\_liang As Variant, arr() As Boolean

If IsMissing(shu\_zu) Then FILTER = CVErr(2015): Exit Function '[shu\_zu]she\_zhi\_que\_sheng\_zhi

If IsMissing(bao\_kuo) Then FILTER = CVErr(2015): Exit Function '[bao\_kuo]she\_zhi\_que\_sheng\_zhi

If IsMissing(kong\_zhi) Then kong\_zhi = CVErr(2000) 'CVErr(2050) '[kong\_zhi]she\_zhi\_que\_sheng\_zhi【lao\_ban\_benOfficehuoWPSbu\_jian\_rong\_shu\_chu\_wei#CALC!，zan\_yong#NUll!dai\_ti】

If IsObject(shu\_zu) Then

    If shu\_zu.Areas.Count > 1 Then FILTER = CVErr(2023): Exit Function Else shu\_zu = shu\_zu.Value 'cai\_yong\_wei\_ruan\_zuo\_fa，ji\_dang\_chuan\_ru\_duo\_ge\_qu\_yu\_shi，shu\_chu\_wei#REF!

End If

If IsArray(shu\_zu) Then Else shu\_zu = Array(shu\_zu) 'fei\_shu\_zu

'de\_dao[shu\_zu]wei\_shu\_da\_xiao

FILTER = Null: FILTER = LBound(shu\_zu, 2): yi\_wei\_xia\_biao = LBound(shu\_zu, 1): yi\_wei\_shang\_biao = UBound(shu\_zu, 1): If yi\_wei\_xia\_biao > yi\_wei\_shang\_biao Then FILTER = shu\_zu: Exit Function 'ruo\_yi\_wei\_shu\_zu[shu\_zu]<wu\_bian\_liang>，bu\_chu\_li

If IsNull(FILTER) Then er\_wei\_xia\_biao = yi\_wei\_xia\_biao: er\_wei\_shang\_biao = yi\_wei\_shang\_biao: ji\_shu = 1 Else er\_wei\_xia\_biao = FILTER: er\_wei\_shang\_biao = UBound(shu\_zu, 2): ji\_shu = yi\_wei\_shang\_biao - yi\_wei\_xia\_biao + 1

'If IsNull(FILTER) Then ji\_shu = 1 Else ji\_shu = yi\_wei\_shang\_biao - yi\_wei\_xia\_biao + 1

'que\_ren[bao\_kuo]

If IsObject(bao\_kuo) Then

    If bao\_kuo.Areas.Count > 1 Then FILTER = CVErr(2023): Exit Function Else bao\_kuo = bao\_kuo.Value 'cai\_yong\_wei\_ruan\_zuo\_fa，ji\_dang\_chuan\_ru\_duo\_ge\_qu\_yu\_shi，shu\_chu\_wei#REF!

End If

If IsArray(bao\_kuo) Then '[bao\_kuo]shi\_shu\_zu

    bian\_liang = Null: bian\_liang = LBound(bao\_kuo, 2): R1 = LBound(bao\_kuo, 1): C1 = UBound(bao\_kuo, 1): If R1 > C1 Then FILTER = bao\_kuo: Exit Function 'ruo\_yi\_wei\_shu\_zu[bao\_kuo]<wu\_bian\_liang>，bu\_chu\_li

    If IsNull(bian\_liang) Then '[bao\_kuo]yi\_wei

        If R1 = C1 Then bao\_kuo = bao\_kuo(R1): GoTo bao\_kuo\_wei\_yi\_ge\_zhi

        If C1 - R1 <> er\_wei\_shang\_biao - er\_wei\_xia\_biao Then FILTER = CVErr(2015): Exit Function 'lie\_shu\_bu\_yi\_zhi

        If IsNull(FILTER) Then '[shu\_zu]yi\_wei

            'ReDim bian\_liang(1 To er\_wei\_shang\_biao - er\_wei\_xia\_biao + 1) As Variant

            ji\_shu = yi\_wei\_xia\_biao - 1

            R1 = R1 - 1

            For C1 = yi\_wei\_xia\_biao To yi\_wei\_shang\_biao

                R1 = R1 + 1

                If IsError(bao\_kuo(R1)) Then FILTER = bao\_kuo(R1): Exit Function Else bao\_kuo(R1) = bao\_kuo(R1) \* 1

                If IsNumeric(bao\_kuo(R1)) Then Else FILTER = CVErr(2015): Exit Function '[bao\_kuo]zhong\_de\_zhi\_fei\_shu\_zhi\_huo\_fei\_bu\_er

                If bao\_kuo(R1) Then ji\_shu = ji\_shu + 1: shu\_zu(ji\_shu) = shu\_zu(C1)

            Next

            If ji\_shu <> yi\_wei\_xia\_biao - 1 Then ReDim Preserve shu\_zu(1 To ji\_shu - yi\_wei\_xia\_biao + 1): FILTER = shu\_zu: Exit Function Else FILTER = kong\_zhi: Exit Function

        Else '[shu\_zu]er\_wei

            'ReDim bian\_liang(1 To ji\_shu, er\_wei\_shang\_biao - er\_wei\_xia\_biao + 1) As Variant

            ji\_shu = er\_wei\_xia\_biao - 1

            er\_wei\_shang\_biao = ji\_shu

            For R1 = R1 To C1

                er\_wei\_shang\_biao = er\_wei\_shang\_biao + 1

                If IsError(bao\_kuo(R1)) Then FILTER = bao\_kuo(R1): Exit Function Else bao\_kuo(R1) = bao\_kuo(R1) \* 1

                If IsNumeric(bao\_kuo(R1)) Then Else FILTER = CVErr(2015): Exit Function '[bao\_kuo]zhong\_de\_zhi\_fei\_shu\_zhi\_huo\_fei\_bu\_er

                If bao\_kuo(R1) Then

                    ji\_shu = ji\_shu + 1

                    For C1 = yi\_wei\_xia\_biao To yi\_wei\_shang\_biao

                        shu\_zu(C1, ji\_shu) = shu\_zu(C1, er\_wei\_shang\_biao)

                    Next

                End If

            Next

            If ji\_shu <> er\_wei\_xia\_biao - 1 Then ReDim Preserve shu\_zu(yi\_wei\_xia\_biao To yi\_wei\_shang\_biao, 1 To ji\_shu - er\_wei\_xia\_biao + 1): FILTER = shu\_zu: Exit Function Else FILTER = kong\_zhi: Exit Function

        End If

    Else '[bao\_kuo]er\_wei

        If R1 = C1 And bian\_liang = UBound(bao\_kuo, 2) Then bao\_kuo = bao\_kuo(R1, bian\_liang): GoTo bao\_kuo\_wei\_yi\_ge\_zhi

        If R1 = C1 Then '[bao\_kuo]er\_wei，yi\_xing

            If UBound(bao\_kuo, 2) - bian\_liang <> er\_wei\_shang\_biao - er\_wei\_xia\_biao Then FILTER = CVErr(2015): Exit Function 'lie\_shu\_bu\_yi\_zhi

            If IsNull(FILTER) Then '[shu\_zu]yi\_wei

                ji\_shu = yi\_wei\_xia\_biao - 1

                yi\_wei\_shang\_biao = yi\_wei\_xia\_biao - 1

                For C1 = bian\_liang To UBound(bao\_kuo, 2)

                    yi\_wei\_shang\_biao = yi\_wei\_shang\_biao + 1

                    If IsError(bao\_kuo(R1, C1)) Then FILTER = bao\_kuo(R1, C1): Exit Function Else bao\_kuo(R1, C1) = bao\_kuo(R1, C1) \* 1

                    If IsNumeric(bao\_kuo(R1, C1)) Then Else FILTER = CVErr(2015): Exit Function '[bao\_kuo]zhong\_de\_zhi\_fei\_shu\_zhi\_huo\_fei\_bu\_er

                    If bao\_kuo(R1, C1) Then ji\_shu = ji\_shu + 1: shu\_zu(ji\_shu) = shu\_zu(yi\_wei\_shang\_biao)

                Next

                If ji\_shu <> yi\_wei\_xia\_biao - 1 Then ReDim Preserve shu\_zu(1 To ji\_shu - yi\_wei\_xia\_biao + 1): FILTER = shu\_zu: Exit Function Else FILTER = kong\_zhi: Exit Function

            Else '[shu\_zu]er\_wei

                ji\_shu = er\_wei\_xia\_biao - 1

                er\_wei\_shang\_biao = ji\_shu

                For C1 = bian\_liang To UBound(bao\_kuo, 2)

                    er\_wei\_shang\_biao = er\_wei\_shang\_biao + 1

                    If IsError(bao\_kuo(R1, C1)) Then FILTER = bao\_kuo(R1, C1): Exit Function Else bao\_kuo(R1, C1) = bao\_kuo(R1, C1) \* 1

                    If IsNumeric(bao\_kuo(R1, C1)) Then Else FILTER = CVErr(2015): Exit Function '[bao\_kuo]zhong\_de\_zhi\_fei\_shu\_zhi\_huo\_fei\_bu\_er

                    If bao\_kuo(R1, C1) Then

                        ji\_shu = ji\_shu + 1

                        For X = yi\_wei\_xia\_biao To yi\_wei\_shang\_biao

                            shu\_zu(X, ji\_shu) = shu\_zu(X, er\_wei\_shang\_biao)

                        Next

                    End If

                Next

                If ji\_shu <> er\_wei\_xia\_biao - 1 Then ReDim Preserve shu\_zu(yi\_wei\_xia\_biao To yi\_wei\_shang\_biao, 1 To ji\_shu - er\_wei\_xia\_biao + 1): FILTER = shu\_zu: Exit Function Else FILTER = kong\_zhi: Exit Function

            End If

        ElseIf bian\_liang = UBound(bao\_kuo, 2) Then '[bao\_kuo]er\_wei，yi\_lie

            If C1 - R1 + 1 <> ji\_shu Then FILTER = CVErr(2015): Exit Function 'xing\_shu\_bu\_yi\_zhi

            'ci\_shi[shu\_zu]bi\_ding\_shi\_er\_wei，qie[shu\_zu]di\_yi\_wei\_de\_ge\_shu（xing\_shu）>1

            'que\_ding\_jie\_guo\_shu\_zu\_de\_xing\_shu。

            ReDim Preserve arr(yi\_wei\_xia\_biao To yi\_wei\_shang\_biao)

            'jiao\_huan 'fu\_zhi\_gei\_yi\_ding\_yi\_de\_bian\_liang\_lei\_xing，ti\_su

            ji\_shu = bian\_liang: bian\_liang = C1: C1 = ji\_shu: ji\_shu = 0: X = yi\_wei\_xia\_biao - 1

            For R1 = R1 To bian\_liang 'C1

                If IsError(bao\_kuo(R1, C1)) Then FILTER = bao\_kuo(R1, C1): Exit Function Else bao\_kuo(R1, C1) = bao\_kuo(R1, C1) \* 1

                If IsNumeric(bao\_kuo(R1, C1)) Then Else FILTER = CVErr(2015): Exit Function '[bao\_kuo]zhong\_de\_zhi\_fei\_shu\_zhi\_huo\_fei\_bu\_er

                X = X + 1: If bao\_kuo(R1, C1) Then ji\_shu = ji\_shu + 1: arr(X) = True

            Next

            If ji\_shu Then ReDim bian\_liang(1 To ji\_shu, 1 To er\_wei\_shang\_biao - er\_wei\_xia\_biao + 1) As Variant: ji\_shu = 0 Else FILTER = kong\_zhi: Exit Function

            For yi\_wei\_xia\_biao = yi\_wei\_xia\_biao To yi\_wei\_shang\_biao

                If arr(yi\_wei\_xia\_biao) Then

                    ji\_shu = ji\_shu + 1 '[bian\_liang]xing\_shu\_lei\_jia

                    X = 0 '[bian\_liang]lie\_shu\_chu\_shi\_hua

                    For C1 = er\_wei\_xia\_biao To er\_wei\_shang\_biao

                        X = X + 1: bian\_liang(ji\_shu, X) = shu\_zu(yi\_wei\_xia\_biao, C1)

                    Next

                End If

            Next

            FILTER = bian\_liang: Exit Function

        Else

            FILTER = CVErr(2015): Exit Function '[bao\_kuo]fei\_dan\_xing\_huo\_fei\_dan\_lie

        End If

    End If

Else '[bao\_kuo]fei\_shu\_zu

bao_kuo_wei_yi_ge_zhi:

    If er\_wei\_xia\_biao = er\_wei\_shang\_biao Or ji\_shu = 1 Then '[shu\_zu]yi\_xing\_huo\_yi\_lie

        If IsError(bao\_kuo) Then FILTER = bao\_kuo: Exit Function

        bao\_kuo = bao\_kuo \* 1

        If IsNumeric(bao\_kuo) Then Else FILTER = CVErr(2015): Exit Function '[bao\_kuo]zhong\_de\_zhi\_fei\_shu\_zhi\_huo\_fei\_bu\_er

        If bao\_kuo Then FILTER = shu\_zu: Exit Function Else FILTER = kong\_zhi: Exit Function

    Else

        FILTER = CVErr(2015): Exit Function

    End If

End If

End Function