Make a formula that has specific-cell-references change every n'th row in Excel

Hi all,

For a university project, I am currently working on a fictional dataset containing information on a charity and their different campaigns (see attachment for a small part of the document).

What I would like to do is the following: for every "group" of 10 rows (10 most recent donations and/or soliciations for 1 donor), I want to check if the 10 dates showcased in column I (dates on which that specific donor contributed an amount to the charity) overlap with the dates in column F (dates on which the donors received a sollication of the charity asking them for a donation). In that way, it would become possible to compare the different types of donor (e.g. male/female, ZIP code, etc.) in terms of how sensitive they are to the donation solicitations made by the charity.

I found a way to check this overlap for the 10 donation dates of the first donor, using the following formula (see column N): =NOT(ISERROR(MATCH(I11,$F$2:$F$11,0)))

However, now I would like to change the values in the formula after every 10 rows, so that that the formula focuses on the next donor. For example: it should become $F$12:$F$21 for donor #7270, $F$22:$F$31 for donor #10620, and so on and so forth.

Is there a way to do this? Of should I use a different type of formula?

Best,
Stan

Hi Stan,

if, per sample, your data is sorted by donor_id (Col A) then one option:

Formula:

Please Login or Register  to view this content.

if data is not sorted this way then you could use:

Formula:

Please Login or Register  to view this content.

or, if your data is small you could use OFFSET which is a little more succinct, syntax wise, but Volatile (so not so great if used in large number, or with lots of further / dependent calculations)

Formula:

Please Login or Register  to view this content.

Your first proposed formula seems to work perfectly XLent. Thank you very much for the help!