Substitute Formula for Cell ID in Range

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,

Uploaded example file as suggested.

Thanks tigeravatar.

This works but I neglected to provide important criteria. I need the sequence to begin again each time the Item Number changes in the "Item" Column. I have attached a second file illustrating the situation called Index Random Example for Forum R2.xls. Sorry about that.

Please advise.

Many thanks.

Thanks tigeravatar.

That worked perfectly.

I am sorry tigeravatar, but I was premature in my congratulations. The output does not validate.

The random selection of the "SO No" needs to be restricted to only those SO No's for each Item Number. This is not happening.

When I compare the SO No Column to the calculated Selected SO Column, the exact same SO No's should appear in the Selected SO Column as in the SO No Column for just that Item Number, only resorted by rank.

Please take another look and let me know if you can help.

Thanks again.

Hi tigeravatar

Yes, you are correct. It did work perfectly. I had two issues that were affecting the output.
1. I didn't notice until today that I had to adjust the formula to encompass the last record of the sheet.
2. I had to split the sheet into 5 seperate sheets because of system resource issues. After all I was dealing with 89000 records.

In the end, it worked just fine.

Thanks again for the help.