Sum, Aggregate, Index, Match, Offset Help

[Bob_ipc]

Hey All, I have gotten myself stumped and in need of some help! I am working on a spreadsheet in where I need a value returned based on a block number and by month, and the data is in two different tabs.
Tab A (Data Entry) I need a Value in Column J based on criteria in Columns B (Month-Year) and F (Block#). The value to go into J and data for Columns B and F are found in Tab B (Rates). How do I get the rates for J?

I currently have =SUMIF(Rates!$B$5:$B$46,'Data Entry'!F4,Rates!$G$5:$G$46) in J which gives me the rates based on Block# but now I would like to add a criteria based on month as the rates change monthly.

This is Tab A

Date In	Truck	Driver	Cutblock	Stageing Yard	Net	Tonne	$/Tonne-hr
2020-10-06	GF4470	Isaak	S260896	4127	47,500	47.50	=SUMIF(Rates!$B$5:$B$46,'Data Entry'!F4,Rates!$G$5:$G$46)
2020-10-06	GF4349	Willy	S260896	4127	48,000	48.00
2020-10-06	GF02	Corny	S260896	4127	48,000	48.00
2020-10-06	GF4324	Rueben	S260896	4127	46,000	46.00

Tab B

		$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr
BLOCK #	Cycle (HR)	Oct-20	Nov-20	Dec-20	Jan-20	Feb-20	Mar-20	Apr-20
S260896	3.1	3.072	3.072
E640879	3.267	3.072	3.072
E640885	3.667	3.072	3.072

Any help would be huge!

Thanks

 

[mamady]

Hi Bob,

Please try the below (adjust the ranges accordingly)

Book1
	A	B	C	D	E	F	G	H
1	Date In	Truck	Driver	Cutblock	Stageing Yard	Net	Tonne	$/Tonne-hr
2	06/10/2020	GF4470	Isaak	S260896	4127	47,500	47.5	3.072
3	06/10/2020	GF4349	Willy	S260896	4127	48,000	48	3.072
4	06/10/2020	GF02	Corny	S260896	4127	48,000	48	3.072
5	06/10/2020	GF4324	Rueben	S260896	4127	46,000	46	3.072
Data Entry

Cell Formulas
Range	Formula
H2:H5	H2	=INDEX(Rates!$A$1:$I$5,MATCH(D2,Rates!$A:$A,0),MATCH(MONTH('Data Entry'!A2),MONTH(Rates!$2:$2),0))

Book1
	A	B	C	D	E	F	G	H	I
1		$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr	$/Tonne-Hr
2	BLOCK #	Cycle (HR)	Oct-20	Nov-20	Dec-20	Jan-20	Feb-20	Mar-20	Apr-20
3	S260896	3.1	3.072	3.072
4	E640879	3.267	3.072	3.072
5	E640885	3.667	3.072	3.072
Rates

Best Regards