Lookup based on a date

[jl2509]

Hi ll,

I am looking to find a solution to returning a value from a sheet based on a date.
Sample sheet attached but basically, I am trying to use the date on the "Calcs" sheet to check if the date falls in the month date on the "Data" sheet and return the £ value if so?

Any thoughts

The sample sheet explains it more.

Any help greatfully received.

[Glenn Kennedy]

One way:

=INDEX(Data!2:2,MATCH(EOMONTH(Calcs!D3,-1)+1,Data!1:1,0))

[alansidman]

=HLOOKUP(DATE(YEAR(D3),MONTH(D3),1),Data!$A$1:$G$2,2,FALSE)

[Sam Capricci]

this hlookup looks like it would work for you... =HLOOKUP(D3,Data!A1:G2,2,TRUE)

[CARACALLA]

Calcs

D4=FILTER(Data!A2:G2,(MONTH(Data!A1:G1)=MONTH(Calcs!D3))*(YEAR(Data!A1:G1)=YEAR(Calcs!D3)),"no data")

[jl2509]

Many Thanks Glenn
I prefer the index math method.

Many good and alternative responses though. Thanks to everyone.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.