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?

 

[AlanY]

your formula will only find the first match, for second or third match etc, have a look of this

INDEX MATCH – multiple results

Table of Contents INDEX MATCH - multiple results INDEX and MATCH - multiple criteria and multiple results INDEX and MATCH - multiple criteria and m

www.get-digital-help.com

 

[hmltnangel]

Thanks ALan,

Its not this either. I have years of service 1-50 along the columns, ages 18-65 down the rows. I can see it matches the row number perfect - row 40, but then it wont find the years of service figure in the column to allow the intersect.

 

[Fluff]

Is this for all years of service or just one?

 

[AlanY]

is this part of the formula identify the column?

MATCH([@[Full years of service]],Sheet2!$A$1:$AX$1,0)

 

[hmltnangel]

Thats it Alan. Years of Service is in A1:AX50

Fluff, it seems to happen from 21 years of service onwards

 

[Fluff]

In that case check that 21 onwards are numbers & not text and that they don't have any leading/trailing spaces, also check the values in the Full years of service column.

 

[hmltnangel]

Exactly my thoughts. First thing I done. Even tried format painter as well to match the formats of the earlier columns. Nothing. Its so weird. Th efull years of service column is a "Rounddown" formula. Would that affect it? It rounds down to 0 digits, so effectively rounds down to the nearest whole number.

 

[Fluff]

As long as they are whole numbers that should not affect it, what happens if you change the 2nd Match to an approximate match?

 

[hmltnangel]

Oddly enough, it works when you do approximate match. I really dont get it. Everything seems fine.