CODEWITHSUNDEEP
excel
user3333198
user3333198

Reputation: 105

Index/match referring to concatenated cell

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?

Upvotes: 0

Views: 121

Answers (2)

user4039065
user4039065

Reputation:

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))

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

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

Upvotes: 1

Related Questions