# Sumproduct with Index/Match

#### natekris8183

##### Board Regular
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:
``=((((((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.

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

Replies
1
Views
227
Replies
1
Views
271
Replies
2
Views
257
Replies
7
Views
369
Replies
9
Views
1K

1,191,196
Messages
5,985,230
Members
439,950
Latest member
Xearo96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back