Return values between dates for a location in Excel

Question

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.

Answer 1

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.

Answer 2

< idea deleted.. no interest >

Answer 3

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