excel - One to many relationship to fetch values in an alternate rows -

i trying fetch dates sheet1 common id (there multiple occurrences in sheet1) using array formula in cell (then dragging right side have dates belong specific id): =if(columns($e2:e2)<=$d2,index(sheet1!$b$2:$b$13,small(if(sheet1!$a$2:$a$13=sheet2!$a2, row(sheet1!$a$2:$a$13)-row(sheet1!$a$2)+1),columns($e2:e2))),"")

but, whenever try insert 1 column (for counting purposes) in b/w columns, formula doesn't work. can't figure out issue, appreciate help?

thank you.

assuming first formula in e2, , cells in row 2 of newly-inserted columns blank, replace 2 instances of:

columns($e2:e2)

in formula in e2 with:

count(1/len($d2:d2))

as way of explanation, take formula in f2, part be:

count(1/len($d2:e2))

(the part having changed naturally being end range reference.)

and let's assume 3 new columns inserted left of column e, means e2, f2 , g2 blank, h2 contain entry in e2, and, formula in i2, above part be:

count(1/len($d2:h2))

clearly wish part continue give 2, since cell should still represent second of our returns.

using len rigorous way determine whether cell empty or not. other functions available purpose, though may give incorrect results depending upon whether blanks in range "genuine" blanks or null string "" result of formulas in cells.

if cell empty, has length of 0. hence, in example, above resolves to:

count(1/{1,0,0,0,2})

(where i've made random assumptions lengths of strings in cells d2 , h2.)

there many ways determine how many non-zeroes there in array. sumproduct one; chose another, logic being above becomes, after reciprocation unity:

count({1,#div/0!,#div/0!,#div/0!,0.5})

and, since count ignores errors in range passed it, above resolves 2, desired.

regards