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 tried to troubleshoot using online solutions, but the problem still persists.

Here is the formula I am using for reference: =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)),"")

Is there anything wrong with the formula? Additionally, the formula used to work perfectly fine before and showed the correct output, but now it only displays blanks.

Thank you

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.

4 Replies

SergeiBaklan
Diamond Contributor
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.
- arjungrewal
  Copper Contributor
  Sep 25, 2023
  Thank you! I totally forgot I had to enter the formula using Ctrl+Shift+Enter. I used your modified formula so I wouldn't have to do that again in the future.
  - SergeiBaklan
    Diamond Contributor
    Sep 25, 2023
    arjungrewal , you are welcome
NikolinoDE
Gold Contributor
Sep 23, 2023
arjungrewal
The formula you provided appears to be a complex array formula that utilizes the INDEX and MATCH functions to retrieve a value from another sheet ('Monthly CES') based on specific conditions. The formula itself seems correct.
If the formula used to work correctly but is now showing blank values, and there have been no changes to the formula itself, here are some troubleshooting steps to consider:
Check Data Ranges:
Verify that the data ranges in the 'Monthly CES' sheet (e.g., D2:D1981, E2:E1981, C2:C1981) still contain the data you expect.
Ensure that there are no hidden rows or columns in the 'Monthly CES' sheet that might affect the formula's calculations.
Data Types:
Confirm that the data types in the referenced cells are consistent with what the formula expects. For example, ensure that 'Monthly CES'!$D$2:$D$1981 contains numeric or text values, and 'Monthly CES'!$E$2:$E$1981, 'Monthly CES'!$C$2:$C$1981 contain the expected values.
Conditional Formatting: Check if there is any conditional formatting applied to the cells in the 'Monthly CES' sheet. Sometimes, conditional formatting rules can hide values.
Errors in Data: Look for any potential errors in the data, such as leading or trailing spaces, extra characters, or non-printable characters, which could affect the matching process.
Workbook Calculation: Ensure that the workbook's calculation settings are set to "Automatic" (Formulas tab > Calculation Options > Automatic). Manual calculation may result in formulas not updating.
External Links: If the formula depends on external data sources or linked workbooks, ensure that the links are up to date and not broken.
Formula Context: Check if there are any cell references within the formula (e.g., L$2, $E4) that may be causing unexpected behavior due to changes in their values or cell references elsewhere in the workbook.
Formula Result: If you are using Excel's calculation modes (e.g., Automatic, Manual), it is possible that the formula result is not displayed because of the calculation mode. Changing it to Automatic should make the formula calculate and display the result.
Excel Version: If you are using an older version of Excel, it is possible that the formula may have limitations or behave differently.
Workbook Corruption: In rare cases, workbook corruption can cause formulas to behave unexpectedly. You may consider creating a copy of the worksheet or workbook and see if the formula works correctly in the new copy.
If you encounter any issues or limitations with the formula in a specific Excel version, here is an alternative formula that should provide the same result and is compatible with various Excel versions:
=IFERROR(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)), "")

If after checking these aspects you still encounter issues, it might be helpful to provide more details about the data and any changes that have occurred in your spreadsheet. This additional information could assist in pinpointing the specific cause of the problem.
The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.
Was the answer useful? Mark them as helpful and like it!
This will help all forum participants.

Forum Discussion

Formula Blank After Calculation

4 Replies

Resources