CODEWITHSUNDEEP
exceldateif-statementexcel-formulaindex-match
Derek Dickson
Derek Dickson

Reputation: 13

Matching multiple number rows and check corresponding dates within 90 days

Essentially what i need to do is see if there is a matching number from A2, in column D. Then check is the corresponding date from A2 which is in B2, is within 90 days of the corresponding date from colum E. Date in B must be >= than E and <91 days from E to count.

i.e. Number 24902 date 12/20/22 - is matched to 3 rows in D, but the date is before any of those in E so it doesnt count. Number 24902 date 3/11/23 is matched to 3 rows in D, and the date is within 90 days of row 3. Therefor it does count.

Sample

I've tried Vlook of course, but since it only checks for the first value on the list, it doesnt see any additional values that may match.

I'm thinking its probably an index match, with if(and( but i can't seem to get it to check multiple rows for a match to see if both D and E meet the criteria.

Upvotes: 0

Views: 155

Answers (3)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27233

You could try using one of the following formulas as per your preference/suit:

enter image description here

The firs option which will return the dates which are within the 90 days, delimited by line feeds, ensure to select wrap text from home ribbon tab:

=MAP(A2:A8,B2:B8,LAMBDA(α,δ,
 LET(ε, FILTER(E2:E8,α=D2:D8,""),
     TEXTJOIN(CHAR(10),1,TEXT(FILTER(ε, (δ>=ε)*(δ<ε+91),""),"m/d/e")))))

The second option which you likely want to see, it will return whether the dates for the matched number is within the 90 days or not to show a note or comment:

enter image description here

=MAP(A2:A8,B2:B8,LAMBDA(α,δ,
 LET(ε, FILTER(E2:E5,α=D2:D5,""),
     IF(ISERR(AND(--FILTER(ε, (δ>=ε)*(δ<ε+91),""))),"Not within 90 Days","Within 90 Days"))))

All the above formulas mentioned above works with MS365 exclusively as well, it doesn't needs to copy down as it will spill for the whole range or array.

Upvotes: 0

DataBunny
DataBunny

Reputation: 185

If you have Excel365, you may use FILTER() to fit your need.

Lets say number_cell is your key number (24902), date_cell is your key date (12/20/22) and target_range is your range of numbers and dates that you need to evaluate.

Step 1:

=FILTER(target_range,CHOOSECOLS(target_range,1)=number_cell)

You will get a 2x3 range where first column is all 24902 and second column are different dates.

Step 2: we process the result of filtering on step 1 (called here filter_result)

=LET(aa,CHOOSECOLS(filter_result,2),FILTER(aa,(aa>=date_cell)*(aa<(date_cell+91)),"Not found"))

We take the second column and filter it again for two conditions (multiplying two condition ranges is the same as AND operation).

LET() is fully optional, it just makes the formula less bulky, because we reference the particular column three times.

Keep in mind that you may get either a single value, an array, or an empty result! For latter case, provide a default value or message (such as "Not found" in this example).

Upvotes: 0

Black cat
Black cat

Reputation: 6107

The formula in cell C9: (it's a CSE formula for older versions) and drag down.

=IF((OR((B9>=IF(A9=D$9:D$12,E$9:E$12,99999))*(B9<IF(A9=D$9:D$12,E$9:E$12,99999)+91))),"COUNT","NO")

enter image description here

Upvotes: 0

Related Questions