Need to add another field to my VLookup formula (or possibly need a new formula)

Good Morning,

To assign work to associates using Term Digits, I currently use a VLookup formula (below) along with a template I paste into an additional sheet.

EX:

Formula used on Sheet1:
=VLOOKUP(RIGHT(A2,2),Sheet3!$A$2:$B$21,2)

Info pasted into Sheet3:
Term Digit Responsible Person
00 Dawn
24 Jennifer
49 Silvana
79 Debbie


Thus, if a file number was 1008729 the formula would put 'Jennifer' because this would be her term digit.

However, I now need the formula to be expanded to also take an an additional column into account (a state column). example below:

State Rep Digits
AK Sotheavy 00-99
AL Carlos 00-99
FL Sotheavy 00-14
FL Robin 15-28
FL Sophany 29-42
FL Danielle 43-56
FL Carlos 57-71
FL Dameyan 72-85
FL Tim 86-99
GA Robin 00-99
HI Tim 00-99
NC Sotheavy 00-14
NC Robin 15-28
NC Sophany 29-42
NC Danielle 43-56


Can anyone help me out with this? I know it has to be possible but i just cant seem to get it right?

Thank you so much!

could you attach the excel file with sample data?

Absolutely, I'll get it together now

Formula as it is now.xlsOld vs New Breakdown.xlsx

I have attached two spreadsheets.

One shows the formula as it is now, please see the first sheet for the formula and the third sheet for the table that enables the formula. Note that the current formula does not account for states.

The other attachment shows the current breakdown used in the formula on sheet1 and the new breakdown with state specific term digits (some states will have 0-99 assigned, while other states will have specific term digit breakdowns)

Thank you!

Good Morning,

I just wanted to follow up on this to ask if anyone has had a similar formula developed or if anyone can assist in pointing me in the right direction.

Thank you.