Index, Rand, ignore Matching

Hello

What i am looking to do is setup a rotating QA table for Team managers and agents, each agent get 6 QA's done a month 5 by other TM's and 1 by their own TM. I want it up to randomly chose TM's but have it ignore or recalculate if the Rand is going to return their TM in the first 5 spots

Right Now i am using - INDEX(TMs!A$2:A$5,RANDBETWEEN(1,COUNTA(TMs!$A$2:$A$5)))
Agents Name is in "B" and their TM is in "D" and the Formula is in "F"

So what i am looking with with the formula is if "F" is going to equal "D" to recalculate so it doesn't

Demo Sheet: QA Order - Demo.xlsx

Try in F2 and copy all the way down:

Formula:

Please Login or Register  to view this content.

Good luck!

Originally Posted by Estevaoba

Try in F2 and copy all the way down:

Formula:

Please Login or Register  to view this content.

Good luck!

I tied this and the only thing it doesn't do is randomize the values when re-running the formulas which is something it would need to do month over month as we clear and reassign them to the TMs to do

Try, =INDEX(TMs!$A$2:$A$5,SMALL(IF(MATCH(D2,TMs!$A$2:$A$5,0)={1,2,3,4},"",{1,2,3,4}),RANDBETWEEN(1,3)))

I was getting there, but josephteh was a bit faster.

Formula:

Please Login or Register  to view this content.

[/FORMULA]

Thank you! both actually worked which is awesome,

You’re welcome. Glad to help.
Thank you for the feedback and for the reputation added.
Have a blessed day.