# INDEX / MATCH Over multiple pages

#### sidmark

##### New Member
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

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### AlanY

##### Well-known Member
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
ABCDEFG
1SupplierABC
2Skip prices
3Post CodeKT1
4Type of Skip6YD
5Price to Customer is110210310
Prices
Cell Formulas
RangeFormula
E5:G5E5=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
CellAllowCriteria
D3List=A!\$B\$35:\$B\$39
D4List=A!\$C\$34:\$L\$34

Book1
ABCDEFGHIJKL
33
344YD6YD8YD8YD Enclosed10YD12YD12YD Enclosed14YD16YD16YD Enclosed
35KT1100110120130140150160170180190
36KT10110120130140150160170180190200
37KT12120130140150160170180190200210
38KT13130140150160170180190200210220
39KT14140150160170180190200210220230
A

Book1
ABCDEFGHIJKL
33
344YD6YD8YD8YD Enclosed10YD12YD12YD Enclosed14YD16YD16YD Enclosed
35KT1200210220230240250260270280290
36KT10210220230240250260270280290300
37KT12220230240250260270280290300310
38KT13230240250260270280290300310320
39KT14240250260270280290300310320330
B

Book1
ABCDEFGHIJKL
33
344YD6YD8YD8YD Enclosed10YD12YD12YD Enclosed14YD16YD16YD Enclosed
35KT1300310320330340350360370380390
36KT10310320330340350360370380390400
37KT12320330340350360370380390400410
38KT13330340350360370380390400410420
39KT14340350360370380390400410420430
C

#### sidmark

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

#### AlanY

##### Well-known Member
That is a great work around thank you for having a look.
you're welcome

Replies
9
Views
104
Replies
6
Views
64
Replies
4
Views
97
Replies
9
Views
128
Replies
14
Views
166

1,127,870
Messages
5,627,364
Members
416,245
Latest member
Xterminat

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back