i am trying to enable disable rows and exclude cells that are empty from the random counter code example:

Enable Rank Random Officer Name Output1
yes 23 0.440135354 4216 Leonardo Vela 1 steven ford
yes 7 0.74673831 4217 Donald Pudlowski 2 leonardo vela
13 0.659526727 4218 3 donald pudlowski
yes 24 0.424849358 4219 Steven Ford 4

this code is similar for each case
code : =RANK(C2,$C$2:$C$5) =RAND() =VLOOKUP(G2,B2:E5,4,0)

i need to modify if row three is not enabled in the output and automatically gets position last or after the enabled data