Forum Discussion

DominiqueKohoron's avatar
DominiqueKohoron
Copper Contributor
May 25, 2022
Solved

INDEX MATCH Formula #N/A Error for One Value in Array - All Others Work

Hi,   Thanks in advance for any assistance you can provide.   I have a spreadsheet that uses INDEX and MATCH to select a pay rate value from an array, based on a grade and level which is calculat...
excel
Formulas and Functions
office 365
  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    May 25, 2022

    DominiqueKohoron Thanks! It sometimes happens that when you calculate a number in Excel that seems to be exactly the same as an number you entered directly, is not found when trying to match it.

    That's caused by minute differences in the decimals in Excels internal memory. Not sure I'm using the correct terms her, but that doesn't matter. To resolve it, you need to round the numbers involved.

     

    I changed the formula in BL12 to:

     

    =IF((AND($H12<BL$2,$H12>BI$2)),ROUND((BI12+0.1),1),BI12)

     

    Note that the part where it calculates the next level is now wrapped in ROUND( calculation, 1)

    Then it works.

     

     

Riny_van_Eekelen's avatar
Riny_van_Eekelen
Platinum Contributor
May 25, 2022

DominiqueKohoron Please show the formula and the context in which it is used. Otherwise it's impossible to find out why it goes wrong.

DominiqueKohoron's avatar
DominiqueKohoron
Copper Contributor
May 25, 2022
How do I upload the sheet?
    • DominiqueKohoron's avatar
      DominiqueKohoron
      Copper Contributor
      May 25, 2022
      https://sexindustrynetwork-my.sharepoint.com/:x:/g/personal/gm_sin_org_au/EdW1i0XpwqRMocGXpk_SAN8BhggWDFOmrHtbN8a6kaQ5mQ?e=NjzsVL
      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor
        May 25, 2022

        DominiqueKohoron Thanks! It sometimes happens that when you calculate a number in Excel that seems to be exactly the same as an number you entered directly, is not found when trying to match it.

        That's caused by minute differences in the decimals in Excels internal memory. Not sure I'm using the correct terms her, but that doesn't matter. To resolve it, you need to round the numbers involved.

         

        I changed the formula in BL12 to:

         

        =IF((AND($H12<BL$2,$H12>BI$2)),ROUND((BI12+0.1),1),BI12)

         

        Note that the part where it calculates the next level is now wrapped in ROUND( calculation, 1)

        Then it works.

         

         

Resources