Need VBA_VBasic help to Identify values among ranges

[pachuken2012]

I’m trying to group trainees in different groups (base on the size of the training sessions)
I have a table with ranges and a their respective group; need to identify if a predefined alphanumeric value assigned to each user (on column I) fit within a specific range (columns E & F) then assign their respective final training group (column G)
I have more than 100 ranges in my current file. Thus, a nested IF formula won’t be able to work.
I don't have experience with VBA and I need to find a solution to avoid assign them manually... (Have more than 5,000 trainees)
Can anyone help with the formula?
Thanks!

[Richard Buttrey]

Hi

First sort your A:G data using A as the primary sort and B as the secondary
Now enter as an array formula (i.e. Ctrl-Shift-Enter) in K2

=INDEX(G:G,MATCH(VALUE(MID(RIGHT(I2,LEN(I2)-4),MATCH(TRUE,ISNUMBER(1*MID(RIGHT(I2,LEN(I2)-4),ROW($1:$9),1)),0),COUNT(1*MID(RIGHT(I2,LEN(I2)-4),ROW($1:$9),1)))),B:B,1),1)

and copy down.

[pachuken2012]

Thanks a lot Rich for your quick response!!
Great formula
Cheers,

[pachuken2012]

Richard
After reviewing in more details your formula (working before with the sample provided) and adding the entire list of ranges; I’m not able to match the value (Column I) with the right group (Column G), in most of the cases.....
I wonder if is related to part of your formula [ROW($1:$9)]…. I can’t figure it out…
As you will see I added more details to the training groups to better differentiate or define unique values and avoid confusion among them.
Can you please review the file attached with additional ranges?
Thanks again!!!