Guru do Excel e Power BI

Boa noite!
Pessoal, estou tentando criar uma planilha de sorteio para aplicação de provas.
A planilha com o sorteio eu até fiz, mas queria fazer a separação pós sorteio dos alunos que irão para determinada sala de prova.
Por exemplo: Na planilha (aba) SORTEIO, coluna F4 até F250 estão todos os alunos sorteados e suas respectivas salas de lotação.
Agora na planilha DISTRIBUIÇÃO, quero que na primeira planilha mostre onde os alunos do 9A irão ficar naquele sorteio, como por exemplo o aluno Arthur que estará na sala 2 ou na sala 7, ou na sala 8 etc.
Preciso na verdade que o excel leia a as informações da planilha SORTEIO e distribua na planilha DISTRIBUIÇÃO.

Fico muito agradecido pela ajuda.

Bom dia
Poderia colocar manualmente uma possível solução para a sua necessidade.
Não consegui entender .
Até
Foxtri

Olá!
Na células F4 em diante estão todos os alunos que foram sorteados aleatoriamente.
Por exemplo os alunos que vão aparecer aleatoriamente nas células F4 até F33 irão fazer a prova na SALA 02. Dentro da sala 2 estarão alunos dos 9A, 9B, 1A, 1B, 2A, 2B, 3A e 3B, até aqui tudo bem.
Mas eu preciso colocar na sala de cada turma em quais salas irão fazer a prova.
Pegando como exemplo a sala do 3MB. (a sala encontra-se ao final de cada nome)

Bom dia
Veja se era isso ??
Até
Foxtri

Foxtri, boa tarde.
Ficou perfeito o que vc fez.
Vc poderia me dizer, o que eu posso ir mudando na formula, para que eu fazer a mesma coisa com as turmas 9B, 1A, 1B, 2A, 2B, 3A e 3B?

=SEERRO(ÍNDICE(SORTEIO!$F$4:$F$300;MENOR(SE((ARRUMAR(DIREITA(SORTEIO!$F$4:$F$300;3))="9A")*(SORTEIO!G4:G300=A$6);LIN(SORTEIO!$F$4:$F$300));LIN($A1))-3;1);"")

Mais uma vez, muito obrigado!

Bom dia.
Nas respectivas colunas é só ir mudando : 9B, 1A, 1B, 2A, 2B, 3A e 3B
=SEERRO(ÍNDICE(SORTEIO!$F$4:$F$300;MENOR(SE((ARRUMAR(DIREITA(SORTEIO!$F$4:$F$300;3))="9B")*(SORTEIO!G4:G300=A$6);LIN(SORTEIO!$F$4:$F$300));LIN($A1))-3;1);"")

=SEERRO(ÍNDICE(SORTEIO!$F$4:$F$300;MENOR(SE((ARRUMAR(DIREITA(SORTEIO!$F$4:$F$300;3))="1A")*(SORTEIO!G4:G300=A$6);LIN(SORTEIO!$F$4:$F$300));LIN($A1))-3;1);"")

=SEERRO(ÍNDICE(SORTEIO!$F$4:$F$300;MENOR(SE((ARRUMAR(DIREITA(SORTEIO!$F$4:$F$300;3))="1B")*(SORTEIO!G4:G300=A$6);LIN(SORTEIO!$F$4:$F$300));LIN($A1))-3;1);"")

e assim por diante

Até
Foxtri