Forum Discussion

Copper Contributor

Sep 22, 2023

Solved

Formula Blank After Calculation

Hi everyone, This specific formula in my spreadsheet is showing blank values (""). The formula does not show any errors and other formulas in the same spreadsheet are working perfectly fine. I tr...

excel

Formulas and Functions

SergeiBaklan

Sep 23, 2023

arjungrewal

Assuming you are not on Excel 365 this formula

=IFNA(
    INDEX( 'Monthly CES'!$D$2:$D$1981,
        MATCH( 1,
            (--L$2 = 'Monthly CES'!$E$2:$E$1981) *
                ($E4 = 'Monthly CES'!$C$2:$C$1981),
            0 )
    ), "" )

most probably works, but requires to be entered with Ctrl+Shift+Enter, i.e. as array formula. To avoid this it could be modified as

=IFNA(
    INDEX( 'Monthly CES'!$D$2:$D$1981,
        MATCH( 1,
            INDEX(
            (--L$2 = 'Monthly CES'!$E$2:$E$1981) *
                ($E5 = 'Monthly CES'!$C$2:$C$1981),
            0, ),
            0 )
    ), "" )

On 365 it could be as

=XLOOKUP( 1,
    (--L$2 = 'Monthly CES'!$E$2:$E$1981) *
    ($E6 = 'Monthly CES'!$C$2:$C$1981),
    'Monthly CES'!$D$2:$D$1981, "")

It looks like you use structured tables, if so even more better to use structured references in calculations.