Use random value from a range, but excluding values from specific cells

Hi guys,

I'm trying to do something which I suspect is very simple, but I have Friday brain and can't figure it out. I have attached a very simplified version of a rota that I'm creating, which shows two officers on duty each day, but every Friday needs an additional officer for emergency cover. I want the emergency cover to be randomly generated from the list of officers, but to never be the same as the officers already on duty that day. So, in my example attached, how do I ensure that F4 is not the same as F2 or F3?

Thanks in advance

Try this:

=INDEX(Table2[Names], LARGE(MATCH(ROW(Table2[Names]), ROW(Table2[Names]))*NOT(COUNTIF($F$2:F3, Table2[Names])), RANDBETWEEN(1, ROWS(Table2[Names])-ROW(A1)+1)))

If you don't have Excel 365, you may need to enter this as an array formula.

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Ah! Bad move ... Over-simplifying your sample data is never a good idea!