combining LOOKUP and IF functions

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%

.

Relevant Pages

  • Re: Automatic Sorting?
    ... LOOKUP functions but finds the value based upon a row and column index ... >>> Is there a way that Excel can automatically sort a> column? ... >>> Scott B. Hogle ...
    (microsoft.public.excel.programming)
  • Re: Lookups
    ... listing of 130 units being built in a building, ... is, just by initially assigning the suite a floorplan type, the subsequent ... were I think my Excel doc should really be in Access... ... I want to be able to, in Access, lookup a value and its corresponding piece ...
    (microsoft.public.access.gettingstarted)
  • RE: combining LOOKUP and IF functions
    ... It may be possible to use Lookup ... different mode of operation (see help in excel). ... I used the VBA language in a probabilty course to model shuffling cards. ... excel spreadsheet and then plot the result of the model. ...
    (microsoft.public.excel.worksheet.functions)
  • Daily Post
    ... One of the handier features of Excel is array formula. ... sometimes returning multiple results, other times single results. ... LOOKUP, both of which can process simple arrays WITHOUT array entry. ... Gnumeric handles the SUMPRODUCT formula, ...
    (comp.apps.spreadsheets)
  • Re: Referencing Help
    ... Kind regards, ... Microsoft MVP - Excel ... I've tried to use the lookup ...
    (microsoft.public.excel.misc)
Loading