Need to match to first three characters in column header...

**mreinhard** · 06-13-2013, 04:32 PM

I have a presentation set of data that feeds off of a pivot table. The presentation data uses Jan, Feb, Mar, etc....

The source data table from Dynamics CRM uses Jan - 2013, Feb - 2013, Mar - 2013 in its column headers...

The formula below works if I change the headers to match each other; however, this is not possible for the final deliverable.

=SUM(INDEX('DataTable'!$C$4:$G$9,,MATCH(B$63,'DataTable!$C$4:$G$4,0)))

Is there a way to modify part of the formula to only look at the first three characters of the source data so that Jan will match to Jan - 2013?

I can't seem to use the LEFT function against a range, so that has been my main issue.

**Jonmo1** · 06-13-2013, 04:34 PM

Sure it can be done, but it's not quite clear

In this formula
=SUM(INDEX('DataTable'!$C$4:$G$9,,MATCH(B$63,'DataTable!$C$4:$G$4,0)))
which of the hilghlighted parts has the longer string (Jan - 2013)

And are the cells actually just text strings, or are they dates custom formatted to only show "MMM" or "MMM - YYYY"

If they are literally just text strings, and it's the C4:G4 that is 'Longer', you can use a wildcard

=SUM(INDEX('DataTable'!$C$4:$G$9,,MATCH(B$63&"*",'DataTable!$C$4:$G$4,0)))