Index/match referring to concatenated cell

0
votes

I am trying to index/match a range with a concatenated date in a cell. The cell '10-year SGS'!F6 in the index/match formula below is a 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 I type the date manually into the concatenated cell, then index/match works. The format for both cells is "date".

Does anyone have an idea?

2
excel

2 Answers

1
votes

If using the typed date works, then replace:

=CONCATENATE(E6,"/",C6,"/",A6)

with either:

=DATE(A6,E6,C6)

or

=DATE(A6,C6,E6)

depending on your regional date system

1
votes

A string that looks like a date is not the same thing as a date. Try creating an actual date with the DATE function.

=DATE(A6, E6, C6)

Alternately, you could wrap your CONCATENATE function with the DATEVALUE function to convert the string looking like a date to an actual date.

=DATEVALUE(CONCATENATE(E6,"/",C6,"/",A6))