Using a formula result to reference a row
 

I have a spread sheet like the following


Item A 0
Item B 0 2
Item C 0 4 5
Item D 0 2 5 7
Item E 1 6 7 8


If I type in a value 'Item C' I can use the match and index functions
to find out which row Item C is on.
Using this row value I should be able to search the row to find out
which column contains 0.


Does anyone know how I can use the result from the first formula in the

second formula. An example would be good.

Using a formula result to reference a row
 

With your Item values in column A:

=MATCH(0,OFFSET(1:1,MATCH("Item C",A:A,FALSE)-1,0),FALSE)

will return the column # for the 0 in the row with Item C. "Item C" can also be replaced by a cell
reference.

HTH,
Bernie
MS Excel MVP


"f1capsicum" wrote in message
oups.com...
I have a spread sheet like the following


Item A 0
Item B 0 2
Item C 0 4 5
Item D 0 2 5 7
Item E 1 6 7 8


If I type in a value 'Item C' I can use the match and index functions
to find out which row Item C is on.
Using this row value I should be able to search the row to find out
which column contains 0.


Does anyone know how I can use the result from the first formula in the

second formula. An example would be good.

Using a formula result to reference a row
 

Thanks, works great