Yield name based off last digit(s) of number

[Seph]

I'm a Parole Supervisor and each month we have a docket of inmates to see the Parole Board. I have three Parole Agents preparing the cases for each month. They are assigned cases based of the inmate's number. Each inmate number starts with two letters and ends with four numbers (NU3644). Mandy is assigned cases based on the inmate number that ends in 1, 2, or 3. Matt is assigned cases based on the inmate number that ends in 4, 5, or 6, and Donna is assigned cases based on the inmate number that ends in 7, 8 or 9. However, many inmate numbers end in zero. In that instance it goes by the preceding number until a positive integer is located. For instance MS3000 would be Mandy's case, and NU3050 would be Matt's case.

I need a formula that would yield the agent's name based off the last digit or in the case of zero the preceding digit(s) of the inmate number.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Seph]

I'm using my work version. It is 2019 MSO

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B
1
2	SK0564	Matt
3	SO9020	Mandy
4	SJ9000	Donna
5	SE1522	Mandy
6	SO9179	Donna
7	SK3307	Donna
8	SE0229	Donna
9	SP9310	Mandy
10	SO9948	Donna
11	NZ1914	Matt
12	SX7009	Donna
13	SE1934	Matt
14	SE1015	Matt
15	SE1253	Mandy
16	TQ4145	Matt
17	SK2675	Matt
18	SD7756	Matt
19	SE1121	Mandy
20	TQ1965	Matt
Main

Cell Formulas
Range	Formula
B2:B20	B2	=CHOOSE(INT((RIGHT(SUBSTITUTE(A2,"0",""))-1)/3)+1,"Mandy","Matt","Donna")

 

[Seph]

You all never cease to amaze me. I know this is probably second nature to you, but I would have never got that formula even with trial and error.

Thanks!!!

 

[Fluff]

You're welcome & thanks for the feedback.