getting next largest price from grid if no exact match found

[keithmct]

I need a reminder folks. I've been using index match for a long time on my price grids and have to re-arrange the grids (from suppliers) from left to right and top to bottom as highest value first and have a "-1" in there so that when exact price isn't found it grabs the next highest price when using my formula:
=IF(I15<>"",INDEX(INDIRECT(E15&AE15&"prices"),MATCH(I15,INDIRECT(E15&AE15&"height"),-1),MATCH(G15,INDIRECT(E15&AE15&"width"),-1)),"0")
But didn't I see somewhere that there had been a change so that you could put something in place of the "-1" and I wouldn't have to muck around with flipping price grids?
There shouldn't be a need to explain the cell references in the above, I just need to know about the possible general INDEX/MATCH improvement.

 

[Peter_SSs]

Would there be any significant difference to stability or speed in the spreadsheet because if XMATCH works, that's good enough for me.

It just seemed logical to me to use a single function. Given the other points you have made I don't see any particular reason to change from XMATCH.

 

[keithmct]

Why not use XLOOKUP then? That will do the XMATCH and the INDEX all in one.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	type	width			height	result
4	Holland	1810			1200	#N/A
5	1
6
7
8	WIDTH	640	840	1040	1240	1440	1640	1840	2140	2440	2590	2740	2890	3040	3190	3400
9	DROP
10	1000	87	99	105	115	125	138	149	163	182	190	199	207	214	255	297
11	1200	96	104	115	126	135	149	161	183	201	209	216	226	236	280	325
12	1400	102	110	125	135	147	159	173	194	212	223	233	242	249	291	332
13	1600	106	123	132	144	155	166	185	208	229	241	250	258	266	309	too big
14	1800	115	130	140	153	166	184	194	227	253	269	286	293	298	339	too big
15	2000	125	139	152	165	177	193	210	275	295	305	317	328	338	383	too big
16	2200	132	147	160	175	191	208	236	288	315	328	339	348	359	403	too big
17	2400	142	158	172	189	204	219	249	304	329	342	355	369	382	432	too big
18	2700	153	169	184	200	216	240	267	325	361	376	391	403	414	460	too big
19	3000	163	183	199	211	233	254	286	340	387	405	421	431	440	484	too big
20	3300	170	198	212	223	251	269	304	357	414	434	451	458	465	508	too big
21	3600	178	211	226	234	268	285	323	373	441	463	482	487	491	531	too big
Sheet1

Cell Formulas
Range	Formula
F4	F4	=XLOOKUP(A4&A5&"prices",B4&A5&"height",E4&A5&"width",,1)

Peter this is what I would roughly need to work. Holland has to have the 1 after it. There are more than 1 holland price grids. The named ranges are holland1height, holland1width and holland1prices. what needs to change to make it get a result

 

[keithmct]

just noticed I have width and height in wrong places but anyway still stumped

 

[Peter_SSs]

You are still going to need INDIRECT if you have multiple ranges that might need to be referenced.
I don't have the named ranges etc to actually test.

 

[keithmct]

sorry thats all wrong - I have to rethink my formula.

 

[keithmct]

You are still going to need INDIRECT if you have multiple ranges that might need to be referenced.
I don't have the named ranges etc to actually test.

see post 12 for named ranges. didn't they used to get specified in XL2BB?

 

[keithmct]

this is why I was just going to stick with XMATCH

 

[keithmct]

In regards to replacing XMATCH with XLOOKUP I came up with this but don't know if its any better. Named ranges are A2:A16 = vertical1height, B1:S1=vertical1width and B2:S16 = vertical1prices. please ignore the yellow cell fill.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		716	946	1177	1407	1637	1867	2097	2327	2557	2787	3018	3248	3450	3678	3906	4134	4361	4589
2	900	77	90	102	114	130	143	156	170	183	193	205	220	235	246	261	274	286	299
3	1050	79	92	105	120	135	147	162	176	187	201	214	230	241	256	271	284	299	313
4	1200	80	95	107	124	137	151	168	183	196	205	223	239	253	268	282	296	313	325
5	1350	82	96	112	127	143	156	174	187	202	214	231	246	262	277	293	308	323	337
6	1500	83	98	114	130	147	162	182	195	208	223	239	256	271	286	302	319	335	350
7	1650	84	99	119	134	151	164	184	201	214	231	246	263	282	297	314	330	347	365
8	1800	85	102	121	137	154	171	191	205	223	238	256	274	289	308	325	340	359	374
9	1950	90	103	126	141	160	176	198	212	230	245	263	283	299	317	335	354	371	388
10	2100	92	106	129	146	163	183	202	218	238	253	272	290	312	329	346	366	384	401
11	2250	94	107	132	148	165	185	205	227	244	261	281	299	317	337	356	374	396	413
12	2400	95	112	135	151	171	193	211	233	250	269	288	308	329	347	366	388	404	425
13	2550	96	114	137	154	176	198	218	239	256	277	296	317	337	358	376	399	420	434
14	2700	98	119	141	159	182	202	227	244	263	283	305	327	347	367	388	411	433	450
15	2850	99	120	146	162	184	205	230	250	271	290	313	335	358	376	399	421	443	466
16	3000	101	121	148	164	187	209	236	256	278	297	319	345	366	388	410	434	453	478
17
18
19			width	height		price
20	vertical	1	2000	1489		182
Sheet1

Cell Formulas
Range	Formula
F20	F20	=XLOOKUP(C20,INDIRECT(A20&B20&"WIDTH"),XLOOKUP(D20,INDIRECT(A20&B20&"HEIGHT"),INDIRECT(A20&B20&"PRICES"),,1),,1)

 

[Peter_SSs]

In regards to replacing XMATCH with XLOOKUP I came up with this but don't know if its any better.

Sorry, I didn't have a good grasp of your data structures and named ranges before. Getting a better idea now and agree that your original INDEX/XMATCH/XMATCH idea would probably be the simplest way to go.

keithmct.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		716	946	1177	1407	1637	1867	2097	2327	2557	2787	3018	3248	3450	3678	3906	4134	4361	4589
2	900	77	90	102	114	130	143	156	170	183	193	205	220	235	246	261	274	286	299
3	1050	79	92	105	120	135	147	162	176	187	201	214	230	241	256	271	284	299	313
4	1200	80	95	107	124	137	151	168	183	196	205	223	239	253	268	282	296	313	325
5	1350	82	96	112	127	143	156	174	187	202	214	231	246	262	277	293	308	323	337
6	1500	83	98	114	130	147	162	182	195	208	223	239	256	271	286	302	319	335	350
7	1650	84	99	119	134	151	164	184	201	214	231	246	263	282	297	314	330	347	365
8	1800	85	102	121	137	154	171	191	205	223	238	256	274	289	308	325	340	359	374
9	1950	90	103	126	141	160	176	198	212	230	245	263	283	299	317	335	354	371	388
10	2100	92	106	129	146	163	183	202	218	238	253	272	290	312	329	346	366	384	401
11	2250	94	107	132	148	165	185	205	227	244	261	281	299	317	337	356	374	396	413
12	2400	95	112	135	151	171	193	211	233	250	269	288	308	329	347	366	388	404	425
13	2550	96	114	137	154	176	198	218	239	256	277	296	317	337	358	376	399	420	434
14	2700	98	119	141	159	182	202	227	244	263	283	305	327	347	367	388	411	433	450
15	2850	99	120	146	162	184	205	230	250	271	290	313	335	358	376	399	421	443	466
16	3000	101	121	148	164	187	209	236	256	278	297	319	345	366	388	410	434	453	478
17
18
19			width	height		price
20	vertical	1	2000	1489		182
Sheet1

Cell Formulas
Range	Formula
F20	F20	=LET(p,A20&B20,INDEX(INDIRECT(p&"prices"),XMATCH(D20,INDIRECT(p&"height"),1),XMATCH(C20,INDIRECT(p&"width"),1)))

see post 12 for named ranges. didn't they used to get specified in XL2BB?

They still do - IF the named ranges are used DIRECTLY in the formulas. Of course here they are used INDIRECTLY in the formulas so don't show.