I’m having trouble combining a INDEX MATCH with multiple criteria with a distribution formula i use
i have 15 unique representatives and 3 work multiple state grouping so 17 total
Availble Reps States worked by each rep J/NJ
David A AZ CA CO ID MT NV OR UT WA WY J
Charles B AZ CA CO ID MT NV OR UT WA WY J
Ryan V AZ CA CO ID MT NV OR UT WA WY J
John G AZ CA CO ID MT NV OR UT WA WY J
Brian G AK HI MI MN MO NH OK RI TN J
Harman O AK HI MI MN MO NH OK RI TN J
Victoria J AK HI MI MN MO NH OK RI TN J
Nigel O AK HI MI MN MO NH OK RI TN J
Sue C TX-NJ AL AR DC GA MS NC VA WV NJ
Clair R TX-NJ AL AR DC GA MS NC VA WV NJ
Becky P TX-NJ AL AR DC GA MS NC VA WV NJ
Sierra D IA IL IN KS KY ND NE NM OH PR SD TX-J WI NJ
Christopher A CT DE FL LA MA MD ME NJ NY PA SC VT NJ
Jack U CT DE FL LA MA MD ME NJ NY PA SC VT NJ
John P AK HI MI MN MO NH OK RI TN J
David A AZ CA CO ID MT NV OR UT WA WY NJ
Sue C TX-NJ AL AR DC GA MS NC VA WV J
2.
i have to assign out 493 files to 17 names based on the above state groupings they work and if the file is a J or NJ the distribution formula i use is =INDEX($G$5:$G$21,INT(ROWS($E$1:E29)/$H$2)) (H2 is the number of files each rep will receive in this case 29)
the distribution formula works great and evenly distributes 29 to each name
3. The index match with criteria only assigned to the first person in each state grouping even though there are at least 3 reps working each state grouping.
so it will assign all 493 files to 5 names and i need it assigned to all 17 names
=INDEX($G$5:$G$21,MATCH("*"&C2&"*"&D2,$H$5:$H$21&$I$5:$I$21,0),1)
is there any way to combine these two formulas or does anyone know a better way to do the distribution based on the 2 criteria where it will assign to all 17 names on the available rep list ?
this is what my sheet looks like any help at all would be GREATLY APPRECIATED
Audit # Assigned Rep (Assignmment FORMULA) Org State J/NJ index with criteria index with criteria + Assignment Formula (PROBLEM)
24185502 David A TN J Brian G INDEX($G$5:$G$21,MATCH("*"&C2&"*"&D2,$H$5:$H$21&$I$5:$I$21,0),INT(ROWS($E$1:E29)/$H$2))
23877157 David A MN J Brian G David A
30828684 David A TN NJ #N/A #N/A
20214950 David A NC NJ Sue C David A
25818650 David A AZ NJ David A David A
31238014 David A MI J Brian G David A
15719390 David A TX NJ Sue C David A
34509249 David A MO J Brian G David A
19965564 David A RI NJ Brian G David A
11308717 David A NH NJ Brian G David A
31083270 David A WA J David A David A
21448204 David A UT NJ David A David A
40933141 David A AL NJ Sue C David A
15448262 David A MS NJ Sue C David A
Bookmarks