Index Match - Random Blank in Calculated Column

[hmltnangel]

Afternoon all,

I have an Index Match Formula which works perfect, except for one radnom result in the middle of a calculated colum in the table.

The formula is as follows:

=IFERROR(INDEX(Sheet2!$A$1:$AX$50,MATCH([@[Age(at anticipated end date)]],Sheet2!$A$1:$A$50,0),MATCH([@[Full years of service]],Sheet2!$A$1:$AX$1,0)),"")

For one cell, this returns blank instead of the excepted result of "27" Evaluating the formula shows its not able to find teh second match in the data, but I have checked formatting etc and cant make it work. It may be that this is the farthest column along. But I just cannot figure out this simple error.

Any suggestions?

 

[Fluff]

That suggests that some of your numbers are not whole numbers.

 

[hmltnangel]

They are though ?

This is the formula to rounddown:
=ROUNDDOWN(SUM([@[End Date (Anticipated)]]-[@[Start Date]])/365,0) and iot gives (in this instance the answer of a whole number of "21". On the tab where the Index Match looks at, the number was typed, not copied in - as 21. Its just refusing to understand unless its "approx"

 

[Fluff]

What happens if you use

=INT(([@[End Date (Anticipated)]]-[@[Start Date]])/365)

 

[hmltnangel]

Exactly the same Fluff. Calculates "21" as the value. Then the Index/Match gives #Value error in the cell

 

[Fluff]

If you are getting a #value error it's not a problem with the match function as that would give #N/A if the value couldn't be found.
Check that your lookup table doesn't have any errors in it.