Forum Discussion
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...
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 Please show the formula and the context in which it is used. Otherwise it's impossible to find out why it goes wrong.