help with lookup

[BrutalLogiC]

hi I am trying to work out a formula for the cells in yellow to return the Rate from the schedule of rates based on the selection of (1) department (2) resource and (3) rate type.

e.g.
if I select Dept1, Resource 1 and Monthly, the rate should be 10,000 in cell I5
if I select Dept2, Resource 5 and Daily, the rate should be 98 in cell I9

I haven't posted the full 'schedule of rates' range as it's a bit large but there are 10 departments, 10 resources and 3 rate types. The rates for each resource are the same regardless of the department but they might change so that's why I have the department column. Hope I am not over complicating it.... if I only had 1 rate type I think easier but I can't work out how to cope with 3

Service Pricing Calculator.xlsx
	B	C			F	G		I
4	No	Department			Resource	Rate Type		Rate
5	1	Dept1			Resource 1	Monthly
6					Resource 3	Daily
7					Resource 7	Hourly
8	2	Dept2			Resource 1	Monthly
9					Resource 5	Daily
10
11	3	Dept3			Resource 1	Monthly
12					Resource 4	Daily
13					Resource 2	Hourly
14	4	Dept4			Resource 7	Monthly
15
16
Pricing

Cells with Data Validation
Cell	Allow	Criteria
C5:C31	List	=Resources!$I$7:$I$11
F5:F31	List	=Resources!$K$7:$K$16
G5:G31	List	=Resources!$M$7:$M$9

Service Pricing Calculator.xlsx
	B	C	D	E	F
4	Schedule of Rates
5			Rate Type
6	Department	Resource	Monthly	Daily	Hourly
7	Dept1	Resource 1	10,000	435	54
8	Dept1	Resource 2	12,000	522	65
9	Dept1	Resource 3	14,000	609	76
10	Dept1	Resource 4	16,000	696	87
11	Dept1	Resource 5	18,000	783	98
12	Dept1	Resource 6	5,000	217	27
13	Dept1	Resource 7	6,000	261	33
14	Dept1	Resource 8	7,000	304	38
15	Dept1	Resource 9	8,000	348	43
16	Dept1	Resource 10	9,000	391	49
17	Dept2	Resource 1	10,000	435	54
18	Dept2	Resource 2	12,000	522	65
19	Dept2	Resource 3	14,000	609	76
20	Dept2	Resource 4	16,000	696	87
21	Dept2	Resource 5	18,000	783	98
22	Dept2	Resource 6	5,000	217	27
23	Dept2	Resource 7	6,000	261	33
24	Dept2	Resource 8	7,000	304	38
25	Dept2	Resource 9	8,000	348	43
26	Dept2	Resource 10	9,000	391	49
27	Dept3	Resource 1	10,000	435	54
28	Dept3	Resource 2	12,000	522	65
29	Dept3	Resource 3	14,000	609	76
Resources

Cell Formulas
Range	Formula
E7:E29	E7	=D7/$D$3
F7:F29	F7	=E7/$E$3

 

[Alex Blakenburg]

When you say you hope you are not overcomplicating things, the items that make it more complicated are:-

• In Pricing not having the Department reference on every line
  To overcome this I added a helper column to put Dept on every line
• In Pricing when I downloaded the XL2BB the Rate Types Daily & Hourly had leading and trailing spaces.
  To overcome this I added a Trim statement to the Column G lookup value

Using the above if you have access to the new Xlookup, this should work.

20210401 Multi Criteria Lookup.xlsx
	A	B	C	D	E	F	G	H	I	J
1		No	Department			Resource	Rate Type	Repeat Dept	Rate
2		1	Dept1			Resource 1	Monthly	Dept1	10000
3						Resource 3	Daily	Dept1	609
4						Resource 7	Hourly	Dept1	32.4
5		2	Dept2			Resource 1	Monthly	Dept2	10000
6						Resource 5	Daily	Dept2	783
7								Dept2
8		3	Dept3			Resource 1	Monthly	Dept3	10000
9						Resource 4	Daily	Dept3
10						Resource 2	Hourly	Dept3	64.8
11		4	Dept4			Resource 7	Monthly	Dept4
12
Pricing

Cell Formulas
Range	Formula
H2:H11	H2	=IF(LEFT(C2,4)="Dept",C2,H1)
I2:I11	I2	=IFERROR(XLOOKUP(1,(Resources!$C$7:$C$29=F2)*(Resources!$B$7:$B$29=H2),XLOOKUP(TRIM(G2),Resources!$D$6:$F$6,Resources!$D$7:$F$29)),"")

 

[BrutalLogiC]

hi Alex thanks for reply but sadly xlookup/office365 is something I don't have, i look at envy at this formula..?

I can add the "Dept1" or equivalent to each row if that makes it easier

Not sure why there are leading and trailing spaces, maybe because the data on the Pricing sheet is coming from a dropdown list? on my version there are no spaces...

 

[Alex Blakenburg]

Here is a SumIfs alternative.

20210401 Multi Criteria Lookup.xlsx
	A	B	C	D	E	F	G	H	I	J
1		No	Department			Resource	Rate Type	Repeat Dept	Rate
2		1	Dept1			Resource 1	Monthly	Dept1	10000
3						Resource 3	Daily	Dept1	609
4						Resource 7	Hourly	Dept1	32.4
5		2	Dept2			Resource 1	Monthly	Dept2	10000
6						Resource 5	Daily	Dept2	783
7								Dept2
8		3	Dept3			Resource 1	Monthly	Dept3	10000
9						Resource 4	Daily	Dept3	0
10						Resource 2	Hourly	Dept3	64.8
11		4	Dept4			Resource 7	Monthly	Dept4	0
12
Pricing

Cell Formulas
Range	Formula
H2:H11	H2	=IF(LEFT(C2,4)="Dept",C2,H1)
I2:I11	I2	=IFERROR( SUMIFS( INDEX(Resources!$D$7:$F$29,0,MATCH(TRIM(G2),Resources!$D$6:$F$6,0)), Resources!$B$7:$B$29,H2, Resources!$C$7:$C$29,F2), "")

 

[BrutalLogiC]

Thanks Alex that does the job.

 

[Alex Blakenburg]

Great. Glad I could help.