INDEX / MATCH Over multiple pages

[sidmark]

I have a work book that I'm trying to use to store supplier prices for 1-15 products over certain postal codes 1-99999999? currently set out like table below
The postcodes are in the B column from 35 down, the products are in 34 C across currently stopping at L but possibly more to be added
PAGE NAME (Supplier A)

33​	B	C	D	E	F	G	H	I	J	K	L
34​		4YD	6YD	8YD	8YD Enclosed	10YD	12YD	12YD Enclosed	14YD	16YD	16YD Enclosed
35​	KT1	185	215	255	270	305	325	335	380	410	440
36​	KT10	195	215	255	270	305	325	335	380	410	440
37​	KT12	195	215	255	270	305	325	335	380	410	440
38​	KT13	195	215	255	270	305	325	335	380	410	440
39​	KT14	195	215	255	270	305	325	335	380	410	440

I am using
=INDEX(Supplier A!$B$2:$AB$999,MATCH($D$3,Supplier A!$A$2:$A$999,0),MATCH($D$4,Supplier A!$B$1:$AB$1,0))

on a separate page to find the price of say a 6yd skip in kt1 the trouble I am having is, I'm adding more suppliers on separate sheets and I would like to pull up the cheapest (and possibly second cheapest) price for the size and postcode inputted in table below

PAGE NAME (PRICES)

		inputted fields below			G	H	I	J
	Skip prices			2	Primary	Supplier	secondary supplier
	Post Code	KT1 (postcode in drop down box)		3	NAME	(would like cheapest supplier here)	NAME	(would like next cheapest supplier here)
	Type of Skip	4YD (product in dropdown box)		4	PRICE		PRICE
	Price to Customer is	185 (above formula in here)

I have tried some MIN variations with my limited knowledge and got warnings and pop ups so fluffed it pretty good. The prices on each supplier sheet will be on the same tables positions to make it uniform. ie c35:L999 on every sheet

I've tried to be as clear and concise as possible but if you need more info don't hesitate to ask

Thanks in advance for all your help.

 

[AlanY]

this use the indirect() function to list out prices for all suppliers (sheet names in Row 1), you can use min() or rank() functions to pick them out

Book1
	A	B	C	D	E	F	G
1				Supplier	A	B	C
2		Skip prices
3		Post Code		KT1
4		Type of Skip		6YD
5		Price to Customer is			110	210	310
Prices

Cell Formulas
Range	Formula
E5:G5	E5	=INDEX(INDIRECT(E1&"!$C:$L"),MATCH($D$3,INDIRECT(E1&"!$B:$B"),0),MATCH($D$4,INDIRECT(E1&"!$C$34:$L$34"),0))

Cells with Data Validation
Cell	Allow	Criteria
D3	List	=A!$B$35:$B$39
D4	List	=A!$C$34:$L$34

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
33
34			4YD	6YD	8YD	8YD Enclosed	10YD	12YD	12YD Enclosed	14YD	16YD	16YD Enclosed
35		KT1	100	110	120	130	140	150	160	170	180	190
36		KT10	110	120	130	140	150	160	170	180	190	200
37		KT12	120	130	140	150	160	170	180	190	200	210
38		KT13	130	140	150	160	170	180	190	200	210	220
39		KT14	140	150	160	170	180	190	200	210	220	230
A

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
33
34			4YD	6YD	8YD	8YD Enclosed	10YD	12YD	12YD Enclosed	14YD	16YD	16YD Enclosed
35		KT1	200	210	220	230	240	250	260	270	280	290
36		KT10	210	220	230	240	250	260	270	280	290	300
37		KT12	220	230	240	250	260	270	280	290	300	310
38		KT13	230	240	250	260	270	280	290	300	310	320
39		KT14	240	250	260	270	280	290	300	310	320	330
B

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
33
34			4YD	6YD	8YD	8YD Enclosed	10YD	12YD	12YD Enclosed	14YD	16YD	16YD Enclosed
35		KT1	300	310	320	330	340	350	360	370	380	390
36		KT10	310	320	330	340	350	360	370	380	390	400
37		KT12	320	330	340	350	360	370	380	390	400	410
38		KT13	330	340	350	360	370	380	390	400	410	420
39		KT14	340	350	360	370	380	390	400	410	420	430
C

 

[sidmark]

That is a great work around thank you for having a look.

 

[AlanY]

That is a great work around thank you for having a look.

you're welcome