Multiply the qty based on hourly rate

[ExcelNewbie2020]

Is there any shorter formula instead of adding multiple vlookups in the below table?..

I want to multiply the qty (in yellow) based on the hourly rate. Please see in blue the expected result..

Thank you.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2																RATE
3	1 HR	1.5 HRS	2 HRS	2.5 HRS	3 HRS+	TOTAL AMOUNT									1 HR	800
4	5	3				7,300.00									1.5 HRS	1100
5			6			8,400.00									2 HRS	1400
6	2		9			11,500.00									2.5 HRS	1800
7		2				2,200.00									3 HRS+	2200
8		3	3			7,500.00
9
Sheet4

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2																RATE
3	1 HR	1.5 HRS	2 HRS	2.5 HRS	3 HRS+	TOTAL AMOUNT									1 HR	800
4	5	3				7300									1.5 HRS	1100
5			6			8400									2 HRS	1400
6	2		9			14200									2.5 HRS	1800
7		2				2200									3 HRS+	2200
8		3	3			7500
Sheet1

Cell Formulas
Range	Formula
F4:F8	F4	=MMULT(--A4:E8,P3:P7)
Dynamic array formulas.

 

[ExcelNewbie2020]

Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2																RATE
3	1 HR	1.5 HRS	2 HRS	2.5 HRS	3 HRS+	TOTAL AMOUNT									1 HR	800
4	5	3				7300									1.5 HRS	1100
5			6			8400									2 HRS	1400
6	2		9			14200									2.5 HRS	1800
7		2				2200									3 HRS+	2200
8		3	3			7500
Sheet1

Cell Formulas
Range	Formula
F4:F8	F4	=MMULT(--A4:E8,P3:P7)
Dynamic array formulas.

thanks again sir.it did worked.. would it be possible to have a formula row by row or not a spilling formula?..so i can copy and paste values the amount whenever the rate changes..

 

[Cubist]

I don't see how that would make a difference but adjust the first reference to a single row only.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2																RATE
3	1 HR	1.5 HRS	2 HRS	2.5 HRS	3 HRS+	TOTAL AMOUNT									1 HR	800
4	5	3				7300									1.5 HRS	1100
5			6			8400									2 HRS	1400
6	2		9			14200									2.5 HRS	1800
7		2				2200									3 HRS+	2200
8		3	3			7500
Sheet3

Cell Formulas
Range	Formula
F4:F8	F4	=MMULT(--A4:E4,$P$3:$P$7)

 

[ExcelNewbie2020]

I don't see how that would make a difference but adjust the first reference to a single row only.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2																RATE
3	1 HR	1.5 HRS	2 HRS	2.5 HRS	3 HRS+	TOTAL AMOUNT									1 HR	800
4	5	3				7300									1.5 HRS	1100
5			6			8400									2 HRS	1400
6	2		9			14200									2.5 HRS	1800
7		2				2200									3 HRS+	2200
8		3	3			7500
Sheet3

Cell Formulas
Range	Formula
F4:F8	F4	=MMULT(--A4:E4,$P$3:$P$7)

it worked.. thank you