Share via

Return values between dates for a location in Excel

Dornan, Paul 65 Reputation points
2025-12-03T12:43:03.8+00:00

Hi everyone, I need your help again.

What formula do I require to return the values from J26 (column 10) onwards against the locations found from B2:B23 (column 2) for each month shown from C1:N1 (row 1).

I’ve manually entered some data for April 2025 for each location to show that to which I’m hoping to achieve.

User's image

Microsoft 365 and Office | Excel | For business | Windows

Answer accepted by question author

Barry Schwarz 5,756 Reputation points
2025-12-03T14:17:31.21+00:00

The following formula seems to do what you describe.

=FILTER($J$26:$J$99,(YEAR($A$26:$A$99)=YEAR(C$1))*(MONTH($A$26:$A$99)=MONTH(C$1))*($B$26:$B$99=$A2),"N/A")

Enter it in cell C1. Change the references to row 99 to the last row containing data. Change the text to whatever you want when no match is found. Copy the updated version down to row 23 and as far to the right as your table goes.

The formula works as long as there are not multiple matches, two entries for the same site in the same month.

Was this answer helpful?

1 person found this answer helpful.

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. IlirU 2,491 Reputation points Volunteer Moderator
    2025-12-04T08:46:40.76+00:00

    User's image

    Hi,

    Try below formula in cell C2:

    =LET(tr, (B2:B23 = TRANSPOSE(B26:B35)), IFERROR(1/ (1 / TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",, BYCOL(C1:N1, LAMBDA(a, TEXTJOIN(",",, BYROW((TRANSPOSE(J26:J35) * tr) * (TRANSPOSE(EOMONTH(+A26:A35, 0)) * tr = EOMONTH(a, 0)), SUM))))), ",", ";"))), ""))

    Change the range in formula as per you need.

    Hope this helps.

    IlirU

    Was this answer helpful?

    0 comments
  2. Dana D 90 Reputation points
    2025-12-04T00:47:49.2766667+00:00

    < idea deleted.. no interest >

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.