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.

     

     

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