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]

I've just thought of a potential fail point for my suggestion above

It will not find the correct result if, for example, you search for PS1 890 x 650 and PS1 is only listed for 1100 x 700 or 1000 x 800, but not 1000 x 700 (closest match for both dimensions).
If each item will always have all possible width / height combinations listed then it should be fine.

Also, SUMIFS will total up any erroneous duplicates in the list so that might need to be allowed for?

 

[Peter_SSs]

I also have a separate worksheet that they all feed over to.

All pricing grids (50+) can be made to the exact same size if need be.

Let's do those two things. In my example I have allowed 10 rows and 5 columns for each grid but you can make that whatever size you need/want so long as they are uniform.
An extra requirement is to put a zero in cells A2, A12 & A22. In your sample you had the text "mm" in that position. If you still want to see that you can put the zero in the cell but custom format the cell as "mm"
I have done that in A22 to demonstrate - the actual value in the cell is still 0.

helplise 2020-08-04 1.xlsm
	A	B	C	D	E
1	PS1
2	0	600	700	800	2000
3	1000	1	4	7	10
4	1100	2	5	8	11
5	1200	3	6	9	12
6
7
8
9
10
11	PRMI
12	0	600	700	800	2000
13	1000	13	16	19	22
14	1100	14	17	20	23
15	1200	15	18	21	24
16
17
18
19
20
21	ABCD
22	mm	500	5000	10000
23	500	5	3	6
24	1000	6	2000	78
25	1500	8	5	54
26
27
28
29
30
Price Grids

Now on the Lookup sheet I have used 3 helper columns (to keep all the formulas a bit simpler). Once populated with their formulas, these helper columns could be hidden.
If you have more grids and/or different sizes, you will need to adjust all the formulas. Value, Help Start and Help Width should be straight-forward enough. In Help Height, the 8 at the end of my formula needs to be 2 less than the number of rows for each grid.

helplise 2020-08-04 1.xlsm
	C	D	E	F	G	H	I
1	Width	Height	Product	Value	Help Start	Help Width	Help Height
2	1800	1200	PS1	12	2	5	3
3	2400	600	ABCD	2000	22	3	2
4	500	1150	PS1	3	2	2	3
5	2000	1200	PRMI	24	12	5	3
Lookup

Cell Formulas
Range	Formula
F2:F5	F2	=INDEX('Price Grids'!A$1:E$30,G2+I2,H2)
G2:G5	G2	=MATCH(E2,'Price Grids'!A$1:A$30,0)+1
H2:H5	H2	=MATCH(C2-0.0001,INDEX('Price Grids'!A$1:E$30,G2,0))+1
I2:I5	I2	=MATCH(D2-0.0001,INDEX('Price Grids'!A$1:A$30,G2):INDEX('Price Grids'!A$1:A$30,G2+8))

 

[helplise]

Thanks Jason. I'll have a play around. Do you know much about the new xlookup? Can you lookup the table to return from the 4th column if the first, second and the third all match to 3 x search criteria? I can do a table to look up the next increment so we can do exact match. ?? Thanks again

 

[Peter_SSs]

Do you know much about the new xlookup?

I tried to use XLOOKUP for your problem but anything that I tried ended up more complex than the standard INDEX/MATCH formulas I ended up suggesting in post 12.

 

[helplise]

Thank you Peter and Jason. I hadn't actually seen post 12 when I replied last time. Not sure how that happened. I'll try as above. Thanks so much.

 

[helplise]

Perfect. Thanks so much Peter.

 

[Peter_SSs]

You're welcome.