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 calculated by an IF AND formula.

 

The problem:

 

Some of the formulas are working. When the grade and level goes up from 4.1 to 4.2 the INDEX and MATCH returns #N/A for the hourly rate for the new grade and level 4.2. However, when the grade and level start at 4.2, the correct value is returned, and the subsequent change to 4.3 causes no issues. When I changed the line with the error to start at a different grade/level the problem occured when changing from 4.3 to 4.4, from 5.1 to 5.2, and from 6.1 to 6.2, so it seem related to going from an odd number to an even one.

 

What I have tried so far, with no luck in fixing my problem:

 

I am using 365 so originally just used the enter key, but I did the control, shift, enter method after the problem arose, just in case.

 

I have cleaned and trimmed the data (then copy and pasted values to relevant cells), and checked that the relevant data is categorised as a number for numbers or text for text.

 

I have checked the grade and level data for hidden decimals.

 

I have a sample version that contains no sensitive information, that I can upload.

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.

     

     

Resources