Search code examples
excelexcel-formulaexcel-2010excel-2007excel-2016

How to Index excluding the header and footer

Is there a way to disregard the header and footer of an indexed data?

I need to dynamically get only the dates only that will be used on another dynamic formula.

My code is not working as $L$2:$L$4 contains Holiday and END text. I am planning to use index so that i can only get the dates.

=NETWORKDAYS.INTL(A2,B2,1,$F$2:$F$4)

Holiday column (Column F) is designed that way so that i can insert a row before END row adjusting all connected formula such as my NetworkDays (screenshot).

enter image description here

EDIT: Adjusted the formula

Solution

  • You could try using the following formula, if you are using MS365

    Using DROP( ) twice.

    enter image description here

    • Formula used in cell C2

    =NETWORKDAYS.INTL(A2,B2,1,DROP(DROP($F$1:$F$3,1),-1))

    You can also use the formula in this way, by sorting the range.

    Using DROP( ) & SORT( )

    enter image description here

    • Formula used in cell C2

    =NETWORKDAYS.INTL(A2,B2,1,DROP(SORT($F$1:$F$3,,-1),2))

    Or, Using TOCOL( )

    enter image description here

    • Formula used in cell C2

    =NETWORKDAYS.INTL(A2,B2,1,TOCOL(--$F$1:$F$3,2))

    You can use OFFSET( ) as well, but note that, since its a volatile function, it does not just recalculate anytime something on the sheet changes, it will recalculate anytime anything on any open excel workbook changes! Posted as an alternative approach.

    enter image description here

    • Formula used in cell C2

    =NETWORKDAYS.INTL(A2,B2,1,OFFSET(F:F,1,,COUNTA(F:F)-2))

    Another alternative approach, which avoids the use of volatile functions:

    enter image description here

    • Formula used in cell C2

    =NETWORKDAYS.INTL(A2,B2,1,$F$2:INDEX(F:F,MATCH(7^89,F:F)))

    Test cases:

    enter image description here

    Note: Based on one's Excel Version, may need to hit the CTRL+SHIFT+ENTER while exiting the edit mode.

    Please refer the screenshot below.

    enter image description here

    Explanation:

    • The number 7 is raised to the power of 8 to create a large number, which is used within MATCH( ) function to search for it, using an approximate match, since it doesn't able to find it, hence returns the last occurrence of the value i.e. 5 which is the last row number of the Holiday range.
    • Since we have got the last position of the Holiday range, now we are wrapping it within INDEX( ) to get the date which returns 7/19/2023 as per screenshot.
    INDEX(F:F,MATCH(7^89,F:F))
    • Now referencing F2 using an Union : to create a range that starts from cell F2 and ends till the last value in the column F, this creates a dynamic range and ensures whenever a new date is added, which will be used in the NETWORKDAYS.INTL( ) function for the optional parameter [holidays] .
    =$F$2:INDEX(F:F,MATCH(7^89,F:F))

    Lastly, if you don't want to hardcode the cell reference F2 then you could use this as well.

    enter image description here

    • Formula used in cell C2

    =NETWORKDAYS.INTL(A2,B2,1,INDEX(F:F,2):INDEX(F:F,MATCH(7^89,F:F)))

    One more way, you could try, its only a variant:

    enter image description here

    • Formula used in cell C2

    =NETWORKDAYS.INTL(A2,B2,1,INDEX(F:F,2):INDEX(F:F,MATCH(2,1/(F:F<>""))-1))