Sumproduct with Index/Match

[natekris8183]

I have a column in a data set, we will call it SalesVolGroupDataRng, that assigns a letter grade, A thru E, to the store based upon its unit sales as a ratio to the average sales in the range of stores. For this example, we will say there are 20 stores. In an allocation table, range $K$9:$K$13, we have cells permitting the user to input a quantity to allocate to individual stores in the corresponding row for their letter group (e.g. input 5 into row 1 of the range to allocate quantities to A). I can determine a corresponding numeric value for the letter by finding the ASCII numeric code of the letter and subtracting it from 64 to determine the row value I'd like to lookup in the $K$9:$K$13 range. Can anyone think of how to pass those array values that correspond to the alpha volume group converted as a numeric position in the alphabet to looking up the corresponding value by row in the $K$9:$K$13 range?

The following formula creates and array of values based upon all letters less than or equal to the letter of the current row of data:

=((((((CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1))>64)*(CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1))<=CODE($E22)))*CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1)))-64)>0)*(((((CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1))>64)*(CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1))<=CODE($E22)))*CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1)))-64)))

For clarification, I offset from the named range because the tables are built to support 2,000 stores where we currently only have 1,000, to permit future expansion. So you can ignore the offset function as it is just limiting the range to look to disallow a lot of null values.