Match Index for multiple grids/products

[helplise]

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!

 

[jasonb75]

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]

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]

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
	A	B	C	D
1	Code	Width	Height	Price
2	PS1	1000	600	100
3	PS1	1000	700	110
4	PS1	1000	800	120
5	PS1	1100	600	120
6	PS1	1100	700	130
7	PS1	1100	800	140
8	PS1	1200	600	130
9	PS1	1200	700	140
10	PS1	1200	800	150
Sheet17

 

[helplise]

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]

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]

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!

 

[Peter_SSs]

The pricing grids are in their own worksheets within a file

One pricing grid per worksheet?
Or all the pricing grids on the one worksheet?

 

[helplise]

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]

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
	E	F	G	H
12	PS1	890	650	110
Sheet17

Cell Formulas
Range	Formula
H12	H12	=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?