INDEX / MATCH Over multiple pages

sidmark

New Member
Joined
Nov 21, 2012
Messages
7
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​
BCDEFGHIJKL
34​
4YD6YD8YD8YD Enclosed10YD12YD12YD Enclosed14YD16YD16YD Enclosed
35​
KT1185215255270305325335380410440
36​
KT10195215255270305325335380410440
37​
KT12195215255270305325335380410440
38​
KT13195215255270305325335380410440
39​
KT14195215255270305325335380410440

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 belowGHIJ
Skip prices2Primary Suppliersecondary supplier
Post CodeKT1 (postcode in drop down box)3NAME(would like cheapest supplier here)NAME(would like next cheapest supplier here)
Type of Skip4YD (product in dropdown box)4PRICEPRICE
Price to Customer is185 (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.
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,238
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top