Forum Discussion

Hiren1993's avatar
Hiren1993
Copper Contributor
Feb 16, 2021
Solved

Value not available error for Index match formula..

Hello,

Please help me for the index match formula use.Please check the sheet 2...some value is showing value not available error with a same formula.

 

Please help...

excel
Formulas and Functions
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Feb 16, 2021

    Hiren1993 Your sheet contains rounding differences. for instance, in F29 you are trying to match 0.91 with 0.910000000000001 in B72. It doesn't. Hence, the NA# error.

    Clean-up column B (i.e. get rid off the extra decimals). In column G you also need to write the formula to become like this:

    =ROUND(SALE!E2,2)

    This ensures that Excel picks-up two decimals from the sales sheet as well.

    A cleaned file is attached.

2 Replies

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Feb 16, 2021

    Hiren1993 Your sheet contains rounding differences. for instance, in F29 you are trying to match 0.91 with 0.910000000000001 in B72. It doesn't. Hence, the NA# error.

    Clean-up column B (i.e. get rid off the extra decimals). In column G you also need to write the formula to become like this:

    =ROUND(SALE!E2,2)

    This ensures that Excel picks-up two decimals from the sales sheet as well.

    A cleaned file is attached.

Resources