Return a value in table based on the date within a range from a second table

[chrisrick1]

I have two tables, the first [TableData] is used to show the how much energy has been used on a given date, and the second [TableCharges] is used to show what the rate is per kWh.
[TableData] has multiple columns, but the relevant ones are [Date]; [Usage]; and [Rate]. [TableCharges] has three columns [StartDate]; [EndDate]; [Rate].

For each row, I want the cell under [TableData[Rate]] to return the [TableCharges[Rate]] where the the lookup value is [TableData[Date]] and it is looking for a match where the [TableData[Date]]>=[TableData[StartDate]] AND [TableData[Date]]<=[TableData[EndDate]].

Ideally I would like to resolve this using a excel formula instead of VBA if possible.

Any help would be appreciated.

Thanks

 

[shift-del]

Hi

Mappe2
	B
4	Rate
5	64,19
Tabelle1

Cell Formulas
Range	Formula
B5	B5	=XLOOKUP([@Date],TableCharges[StartDate],TableCharges[Rate],,1,1)

 

[chrisrick1]

Thanks for your response but I've tried getting xlookup to work and not had any success.
This is the [TableData]

This is [TableCharges[Rate]]

date format is dd/mm/yyyy

I was the Charging Rate should be 0.291666667 but as you can see the formula returns 0.41666667

Thanks

 

[chrisrick1]

I have managed to figure this out using adapting a formula posted in a different thread...

=IFERROR(INDEX(TableCharges[Rate],AGGREGATE(15,6,(ROW(TableCharges[Rate])-ROW(TableCharges[#Headers]))/((TableCharges[Start Date]<=[@[Month Ending]])*(TableCharges[End Date]>=[@[Month Ending]])),1)),"Not found")

The solution came from here. Lookup value within range

Thankyou to every one how has looked at this for me.
Cheers

 

[shift-del]

Just change the second to last 1 to -1.

=XLOOKUP([@Date],TableCharges[StartDate],TableCharges[Rate],,-1,1)

 

[Peter_SSs]

@chrisrick1
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)