Hello Excel Experts,

I have a large sheet containing 89,000 records. I want to be able to substitue the Cell ID in the Range of a INDEX(RAND) formula based on the content of the "Item Split" column. Is this at all possible?

Example below: The Random Nos column below contains the funtion RAND(). The Select SO column below contains the combined function of =INDEX($C$2:$C$13,RANK(D2,$D$2:$D$13)).

Item Split Item SO No Random Nos Selected SO
2 06038 3589640 0.539426323 3633384
1 06038 3606267 0.542146952 3628200
1 06038 3616141 0.109259033 5472190
1 06038 3628200 0.127723086 5471537
1 06038 3633384 0.301287132 3634581
1 06038 3634581 0.086179261 7235466
1 06038 5470075 0.635229821 3616141
1 06038 5471537 0.093626961 5475256
1 06038 5472190 0.638983519 3606267
1 06038 5475256 0.2037447 5470075
1 06038 7235466 0.053662117 7237487
3 06038 7237487 0.879992684 3589640

I want to substitue a formula in the combined function that will select the Row Number into the range based on the "Item Split" column, where IF "Item Split" = 2, then use that row number, Else IF "Item Split" = 3, than use that row number.

Conceptually, the formula would look like: =INDEX($C$(formula):$C$(formula),RANK(D2,$D$(formula):$D$(formula)))

Pleae help. This would help me emensly in managing this large amount of data and save hous of time.

Thanks,