Match Index for multiple grids/products

helplise

New Member
Joined
Jul 30, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi all. I need to look up a value from a grid based on width (column headers) and height (row headers). I have worked out how to use the match and index formulas to do it. It's to look up pricing but I have around 50 product codes each with their own pricing grid. I'm unsure how to direct it to the correct grid based on product code. As a workaround, I have the value returned for each of the 50 and do a Hlookup to return the value for the correct product code. Can anyone tell me how to do it more efficiently? Thanks so much!
 

Attachments

  • Excel query.PNG
    Excel query.PNG
    7.4 KB · Views: 10

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,919
Office Version
  1. 365
Platform
  1. Windows
That layout is a bad idea, you could possibly do it with volatile functions, but that would have to assume that all of the grids are identical in size.

The better way (in my opinion) is to set up the source data would be one table with 4 columns, Product - Height - Width - Price
Then use SUMIFS to pull the correct result from that list.
 

helplise

New Member
Joined
Jul 30, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Thanks for your response Jasonb75.
Layouts attached are just an example. The first table is it's own worksheet that has many many columns of data input required/calculated per item, but is required for 30+ items, therefore, all data must be across the row.
The pricing grids are in their own worksheets within a file. All pricing grids (50+) can be made to the exact same size if need be. How can that be of help? Thank you :)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,919
Office Version
  1. 365
Platform
  1. Windows
he pricing grids are in their own worksheets within a file. All pricing grids (50+) can be made to the exact same size if need be. How can that be of help?
Ideally it's the grids that need to be changed (completely), as I said, you could 'possibly' do it if they were all the same size, but even then it will still be a mess (and that's assuming that it will even work at all). To be honest, I personally wouldn't even attempt anything with that layout, such things are good on paper for a visual search, but very bad for an excel formula.

If possible, using a list format like the example below would allow for a very simple formula.

Book1
ABCD
1CodeWidthHeightPrice
2PS11000600100
3PS11000700110
4PS11000800120
5PS11100600120
6PS11100700130
7PS11100800140
8PS11200600130
9PS11200700140
10PS11200800150
Sheet17
 

helplise

New Member
Joined
Jul 30, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks Jason. I do have that layout in a previous version so it would be easy for me to use the other format. I've used simple widths and heights for the example but they also vary from product to product. eg w 830, 1030, 1270. We round up to the next increment for both width and height. eg if we were after price for PS1 980w x 650h, therefore return result from C3. What sort of formula do you recommend? Thank you
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,919
Office Version
  1. 365
Platform
  1. Windows
Do you have the dynamic array functions (SORT, FILTER, UNIQUE) in your version of 365? I think that they should be available to all now but not 100% certain.
I'll give it a bit of thought to see if I can come up with an efficient method without them in case you need it with an older version of excel, but I think that they will be the most useful.
The methods that I had in mind originally didn't allow for rounding of the dimensions.
 

helplise

New Member
Joined
Jul 30, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks Jason. I just checked and I do have those in 365.
I can think of a few ways to do it but just think there must be more efficient ways. I'll have a play and see how those 3 you mentioned can work for me.
Thanks Jason. Much appreciated!
 

helplise

New Member
Joined
Jul 30, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
They're provided individually so I put them into their own worksheets. I also have a separate worksheet that they all feed over to. So either way. Thanks
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
11,919
Office Version
  1. 365
Platform
  1. Windows
Can it be set up with all products in one continuous list (one sheet) in the format that I suggested in post 4?

With the rounding requirement, I don't think that there will be a perfect solution, but I think that using that format for the data source would probably allow for the 'least inefficient' formulas.

Having given it a bit of thought through the day, using the table from post 4, this is the most efficient method that I can think of that allows for rounding up.

Book1
EFGH
12PS1890650110
Sheet17
Cell Formulas
RangeFormula
H12H12=SUMIFS(D:D,A:A,E12,B:B,MINIFS(B:B,A:A,E12,B:B,">="&F12),C:C,MINIFS(C:C,A:A,E12,C:C,">="&G12))


Any thoughts, Peter?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,485
Messages
5,596,442
Members
414,065
Latest member
kamlkham

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