excel - Index/match referring to concatenated cell -

i trying index/match range concatenated date in cell. cell '10-year sgs'!f6 in index/match formula below concatenated date.

the date formula is:

=concatenate(e6,"/",c6,"/",a6).

the index/match formula is:

=index('10-year us'!$b$12:$b$4427,match('10-year sgs'!f6,'10-year us'!$a$12:$a$4427,0))

strangely, if type date manually concatenated cell, index/match works. format both cells "date".

does have idea?

a string looks date not same thing date. try creating actual date date function.

=date(a6, e6, c6)

alternately, wrap concatenate function datevalue function convert string looking date actual date.

=datevalue(concatenate(e6,"/",c6,"/",a6))

Script