Counting number of cells between two variable in anywhere in list in sequence in excel

Question

I am trying to build a formula in excel to pass two text values to match in a list and find number of cells between them. The count needs to go in sequence 1 to 27 and if one variable is at 27 row and the other on 6th row then the count should be (27, 1, 2, 3, 4, 5, 6).

I have two columns, ColA with sequence number and ColB with text. I am using another two columns to reference the text in ColB as a list to select two variables.

I am then using index formula to pull the index number from the ColA and then using the index to create the dynamic range and count the number of cells between the two however, not getting anywhere.

here are my formulas For index =INDEX($E$4:$F$30, MATCH(I3,$F$4:$F$30,0),1) For count = =COUNT(INDIRECT("E"&J3&":E"&J4))

I am sure there is a better way to go about this but i am drawing blanks and hope someone can help me out.

Your helps is greatly appreciated.

Thanks

Answer 1

For such a calculation, the INDEX() function is not needed - the value returned by the MATCH() function is already the index of the element in the sequence. So having received the indices of both the desired strings, you will find the distance between them (in cells) by the usual subtraction:

MATCH(I3;$F$4:$F$30;0)-MATCH(H3;$F$4:$F$30;0)

This works well as long as the second search string is below the first string - the index difference will always be positive. To handle the reverse situation, use the trick: add the length of the sequence to the result, and take the remainder after dividing by the same number, by the length of the sequence, using the MOD() function. At the end, add 1 to the result to count both cells with the search strings:

=MOD(MATCH(I3;$F$4:$F$30;0)-MATCH(H3;$F$4:$F$30;0)+COUNTA($F$4:$F$30);COUNTA($F$4:$F$30))+1

Or - if the length of the sequence will never change and will always be 27 - you can write it without the COUNTA() function:

=MOD(MATCH(I3;$F$4:$F$30;0)-MATCH(H3;$F$4:$F$30;0)+27;27)+1