excel - match criteria and repeat value -
i'm trying create dynamic formula match location
, start_time
, copy value
based on replicate
count.
i'm able index/match location
, start_time
value
unable include dynamic formula have value
replicate
sample data:
ideal outcome:
any suggestions appreciated!
so know first value should go, i'll leave you.
to fill voids in between use formula this:
=if(row()-1="";"";if(row()<=row($f$2)+index($a$2:$d$7;match($f$1;$a$2:$a$7);4)-1;index($a$2:$d$7;match($f$1;$a$2:$a$7);3);0))
here assumed sample data in a1 d7 , ideal outcome starts in e1 , filled 0.12
in cell f2. formula entered in f3 , dragged down. thing need adapt replace absolute reference f2 formula find cell values insert first data.
the first part of formula:
=if(row()-1="";"";
checks if cell directly above cell formula in blank. if blank returned, otherwise rest of formula executed.
the second if statement:
if(row()<=row($f$2)+index($a$2:$d$7;match($f$1;$a$2:$a$7);4)-1;
checks if current row number less or equal first row number value occured (cell f2) plus amount of repetitions minus 1 (as there 1 value in f2)
the last part of formula:
index($a$2:$d$7;match($f$1;$a$2:$a$7);3);0))
then fills in appropriate value using index(match())
against input data.
Comments
Post a Comment