excelsearchindexingfilterexcel-formula

How can I get the nth value from table headers with multiple search criteria

I am trying to return only table headers that start with SECTION and do not end with QUALITY. I can get the ones that start with SECTION but trying to apply an AND(NOT()) function gives a #CALC! error.

Table headers are: SECTION_TITLE1, SECTION_TITLE1_QUALITY, SECTION_TITLE2, SECTION_TITLE2_QUALITY, etc.

I use _QUALITY for other formulas and for description.

Working but incomplete formula:

=INDEX(FILTER(INDIRECT($D$10&"[#Headers]"),ISNUMBER(SEARCH("SECTION*",INDIRECT($D$10&"[#Headers]")))),1)

Attempted formulas:

=INDEX(FILTER(INDIRECT($D$10&"[#Headers]"),ISNUMBER(SEARCH(AND("SECTION*",NOT("*QUALITY")),INDIRECT($D$10&"[#Headers]")))),1)

=INDEX(FILTER(INDIRECT($D$10&"[#Headers]"),ISNUMBER(AND(SEARCH("SECTION*",INDIRECT($D$10&"[#Headers]")),NOT(SEARCH("*QUALITY",INDIRECT($D$10&"[#Headers]")))))),1)

=INDEX(FILTER(INDIRECT($D$10&"[#Headers]"),AND(ISNUMBER(SEARCH("SECTION*",INDIRECT($D$10&"[#Headers]"))),NOT(ISNUMBER(SEARCH("*QUALITY",INDIRECT($D$10&"[#Headers]")))))),1)
Solution

  • Get n-th Excel Table Header With 2 Criteria

    enter image description here

    Begin's with "Section" (A13)

    =LET(h,INDIRECT($D$10&"[#Headers]"),ISNUMBER(SEARCH("Section*",h)))

    Doesn't end with "Quality" (A15)

    =LET(h,INDIRECT($D$10&"[#Headers]"),ISERR(SEARCH("*Quality",h)))

    Product of Both (converts to 1s and 0s) (A17)

    =LET(h,INDIRECT($D$10&"[#Headers]"),
ISNUMBER(SEARCH("Section*",h))*ISERR(SEARCH("*Quality",h)))

    FILTER Applied (A19)

    =LET(h,INDIRECT($D$10&"[#Headers]"),
FILTER(h,ISNUMBER(SEARCH("Section*",h))*ISERR(SEARCH("*Quality",h))))

    INDEX Added (n=1) (A21)

    =LET(n,1,h,INDIRECT($D$10&"[#Headers]"),
INDEX(FILTER(h,ISNUMBER(SEARCH("Section*",h))
    *ISERR(SEARCH("*Quality",h))),n))

    INDEX Added (n=2) (A23)

    =LET(n,2,h,INDIRECT($D$10&"[#Headers]"),
INDEX(FILTER(h,ISNUMBER(SEARCH("Section*",h))
    *ISERR(SEARCH("*Quality",h))),n))

    Lambda Formula (use in Name Manager) (A25)

    =LAMBDA(n,table,begins,ends_not,LET(
    h,INDIRECT(table&"[#Headers]"),
INDEX(FILTER(h,ISNUMBER(SEARCH(begins&"*",h))
    *ISERR(SEARCH("*"&"ends_not",h))),n)))

    Lambda Function "GetNthHeaderYBNE" (Usage) (n=1) (A27)

    =GetNthHeaderYBNE(1,$D$10,"Section","Quality")