I'm trying to use a state transition matrix in Excel. I randomly generate a
percentage and want to lookup the value in a corresponding table where this
value would fall if we sum across the row. For example, the first randomly
generated value is 13.5% and we are starting in state 6. Therefore, I want a
formula that looks in row 6 (starting state) and determines which column
(1-6) has the value 13.5% if you sum the values across (in this case the
answer is 5). Then 5 becomes the new starting state and so on. Is there a
formula or combination of formulas in Excel that can carry out this complex
function?
Thank you.

State transition matrix
1 2 3 4 5 6
1 85.00% 10.50% 3.10% 0.74% 0.66% 0.00%
2 8.90% 79.00% 9.20% 1.70% 1.20% 0.00%
3 1.00% 7.50% 73.00% 15.00% 2.50% 1.00%
4 0.00% 1.00% 13.00% 76.00% 9.00% 1.00%
5 0.00% 2.00% 5.00% 6.00% 71.00% 16.00%
6 0.00% 1.00% 1.00% 6.00% 9.75% 82.00%