How to approach this - multiple lookups?

[MOB]

I am working on a little project to try to help quickly establish a price from a supplier's price spreadsheet.

So on another tab I would enter the following;

- Zone number
- Type
- Number of pallets (1,2,3,4 above)
- 24hr or 48hr

And the price should be pulled from the above data.

What is the best approach to this? I'd rather avoid changing the source data or adding helper data if possible!

TIA

 

[Fluff]

Can you please post your data using the XL2BB add-in, It saves members having to re-create your data. Thanks

Also what version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[MOB]

Thanks

BSI PALLET RATES APRIL 2020 - MARCH 2021 V1 - EXCEL VERSION.xlsx
	A				E	F	G	H	I	J	K	L	M
9						1		2		3		4
10	ZONE				TYPE	24HR	48HR	24HR	48HR	24HR	48HR	24HR	48HR
11	1				FULL	£26.50	£24.00	£52.00	£47.00	£76.50	£69.00	£100.00	£90.00
12					EURO	£25.00	£22.50	£49.00	£44.00	£72.00	£64.50	£94.00	£84.00
13					HALF	£22.50	£20.00	£44.00	£39.00	£64.50	£57.00	£84.00	£74.00
14					QUARTER	£20.00	£18.00	£39.00	£35.00	£57.00	£51.00	£74.00	£66.00
15					MICRO	£18.00		£35.00		£51.00		£66.00
16
17	2				FULL	£39.29	£33.69	£77.58	£66.38	£114.87	£98.07	£151.16	£128.76
18					EURO	£36.49	£32.58	£71.98	£64.16	£106.47	£94.74	£139.96	£124.32
19					HALF	£33.69	£31.46	£66.38	£61.92	£98.07	£91.38	£128.76	£119.84
20					QUARTER	£31.46	£29.21	£61.92	£57.42	£91.38	£84.63	£119.84	£110.84
21					MICRO	£28.09		£55.18		£81.27		£106.36
22
23	3				FULL	£53.30	£42.66	£105.60	£84.32	£156.90	£124.98	£207.20	£164.64
24					EURO	£52.18	£42.10	£103.36	£83.20	£153.54	£123.30	£202.72	£162.40
25					HALF	£51.07	£41.55	£101.14	£82.10	£150.21	£121.65	£198.28	£160.20
26					QUARTER	£49.95	£40.98	£98.90	£80.96	£146.85	£119.94	£193.80	£157.92
27					MICRO	£47.70		£94.40		£140.10		£184.80
MASTER

Cell Formulas
Range	Formula
I11:I14,H23:H27,I23:I26,H17:H21,I17:I20,H11:H15	I11	=G11*2-1
K11:K14,J23:J27,K23:K26,J17:J21,K17:K20,J11:J15	K11	=G11*3-3
M11:M14,L23:L27,M23:M26,L17:L21,M17:M20,L11:L15	M11	=G11*4-6

 

[Fluff]

Thanks for that.
Forgot to ask what version of Excel are you using?
Please update your account details to show this, as it affects which functions you can use.

 

[MOB]

365, updated now thanks

 

[Fluff]

Many thanks for that.
I would suggest create named ranges for each table & then you can use a Vlookup, which is probably the simplest way.

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1
2
3
4
5
6
7
8
9						1		2		3		4
10	ZONE				TYPE	24HR	48HR	24HR	48HR	24HR	48HR	24HR	48HR				Zone	2	57.42
11	1				FULL	26.5	24	52	47	76.5	69	100	90				Type	quarter
12					EURO	25	22.5	49	44	72	64.5	94	84				Pallets	2
13					HALF	22.5	20	44	39	64.5	57	84	74				Service	48hr
14					QUARTER	20	18	39	35	57	51	74	66
15					MICRO	18		35		51		66
16
17	2				FULL	39.29	33.69	77.58	66.38	114.87	98.07	151.16	128.76
18					EURO	36.49	32.58	71.98	64.16	106.47	94.74	139.96	124.32
19					HALF	33.69	31.46	66.38	61.92	98.07	91.38	128.76	119.84
20					QUARTER	31.46	29.21	61.92	57.42	91.38	84.63	119.84	110.84
21					MICRO	28.09		55.18		81.27		106.36
22
23	3				FULL	53.3	42.66	105.6	84.32	156.9	124.98	207.2	164.64
24					EURO	52.18	42.1	103.36	83.2	153.54	123.3	202.72	162.4
25					HALF	51.07	41.55	101.14	82.1	150.21	121.65	198.28	160.2
26					QUARTER	49.95	40.98	98.9	80.96	146.85	119.94	193.8	157.92
27					MICRO	47.7		94.4		140.1		184.8
28
Master

Cell Formulas
Range	Formula
S10	S10	=VLOOKUP(R11,CHOOSE(R10,Zone_1,Zone_2,Zone_3),2*R12+(R13="48HR"),0)
I11:I14,H23:H27,I23:I26,H17:H21,I17:I20,H11:H15	I11	=G11*2-1
K11:K14,J23:J27,K23:K26,J17:J21,K17:K20,J11:J15	K11	=G11*3-3
M11:M14,L23:L27,M23:M26,L17:L21,M17:M20,L11:L15	M11	=G11*4-6

Named Ranges
Name	Refers To	Cells
Zone_1	=Master!$E$10:$M$15	S10
Zone_2	=Master!$E$17:$M$21	S10
Zone_3	=Master!$E$23:$M$27	S10

 

[MOB]

That looks great, I'll have a play back in the office tomorrow

Many thanks

 

[Fluff]

Ok

 

[MOB]

That works perfectly Many thanks for your help!

 

[Fluff]

You're welcome & thanks for the feedback.