INDEX & MATCH with INDIRECT Function

[IDRIS86]

Hi I tried to match the argument in the below formula to -1 to pull the higher values than the look up value. But its not working. Only with Match argument 0 & 1 works. But I need to pick value higher than my reference. Could you assist.

Below the function,

=INDEX(INDIRECT(Table10[@MODEL]&"A"),MATCH(BC28,INDIRECT(Table10[@MODEL]&"H"),0),MATCH(BB28,INDIRECT(Table10[@MODEL]&"W"),0))

 

[Alex Blakenburg]

You would need to provide an XL2BB version of Table10 and the values in BC28 & BB28 and your expected result.
On the face of it the use of Indirect doesn't seem to add any value.

 

[IDRIS86]

You would need to provide an XL2BB version of Table10 and the values in BC28 & BB28 and your expected result.
On the face of it the use of Indirect doesn't seem to add any value.

Hi Alex,
Thanks.
The table is made up a Dimensions called Width in column & Height in Row. If I enter lookup values let's say 329 in row & and 430 in Column. If this specific sizes are not in table, then I would need the corresponding values of next higher available sizes, let say 356 in Row and 457 in column.
I dont know much formula to pick values for a referred size from a table located in other tabs. I referred this online & applied it. It works well & brought the required values. Only the concern is it doesnt go to next available higher sizes. I added both table 10 & the sheet where the values to be pulled. Kindly assist. Also I attached snapshot of my workbook how it looks.

Table 10

Work sheet.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
10	SIZE	102	152	203	254	305	356	406	457	508	559	610	660	2900	2950	3000	3048
11	102	53	59	62	66	69	72	75	82	85	89	92	98	420	420	420	420
12	152	56	59	62	66	72	75	79	82	85	92	95	98	433	433	433	433
13	203	59	62	66	72	75	79	85	89	92	98	102	105	459	459	459	459
14	254	59	66	69	72	75	82	85	89	95	98	105	108	473	473	473	473
15	305	62	69	72	79	82	89	92	95	102	108	112	115	512	512	512	512
16	356	66	72	75	82	85	92	95	102	108	112	118	125	538	538	538	538
17	406	69	75	79	85	92	95	102	108	112	118	125	131	564	564	564	564
18	457	72	79	85	92	95	102	108	115	118	128	131	138	604	604	604	604
19	508	75	82	89	95	102	108	115	121	128	135	141	148	643	643	643	643
20	559	79	85	92	98	105	112	118	125	131	141	148	154	669	669	669	669
21	610	82	89	95	102	108	115	121	128	135	144	151	158	683	683	683	683
22	660	85	92	98	105	112	121	128	135	141	151	158	164	722	722	722	722
23	711	89	95	102	112	118	125	135	141	148	158	164	174	761	761	761	761
24	762	92	98	105	115	121	131	138	148	154	164	171	180	788	788	788	788
25	813	95	102	112	118	128	135	144	151	161	171	180	187	827	827	827	827
26	864	95	105	115	121	131	141	148	158	167	177	187	194	853	853	853	853
27	914	98	108	118	128	135	144	154	164	174	184	194	200	879	879	879	879
28	950	151	164	177	190	203	217	230	243	256	269	282	295	1286	1286	1286	1286
29	1000	151	164	177	190	203	217	230	243	256	269	282	295	1286	1286	1286	1286
30	1050	158	171	184	197	210	223	236	249	263	282	295	308	1339	1339	1339	1339
31	1100	158	171	184	197	210	223	236	249	263	282	295	308	1339	1339	1339	1339
32	1150	164	177	190	203	217	230	243	256	269	289	302	315	1365	1365	1365	1365
33	1200	164	177	190	203	217	230	243	256	269	289	302	315	1365	1365	1365	1365
34	1250	171	184	197	210	223	243	256	269	282	302	315	328	1444	1444	1444	1444
35	1300	171	184	197	210	223	243	256	269	282	302	315	328	1444	1444	1444	1444
36	1350	177	190	203	223	236	249	269	282	295	315	328	348	1523	1523	1523	1523
37	1400	177	190	203	223	236	249	269	282	295	315	328	348	1523	1523	1523	1523
38	1450	184	197	210	230	243	263	276	295	308	328	341	361	1575	1575	1575	1575
39	1500	184	197	210	230	243	263	276	295	308	328	341	361	1575	1575	1575	1575
40	1550	190	203	223	236	256	269	289	302	322	341	361	374	1654	1654	1654	1654
41	1600	190	203	223	236	256	269	289	302	322	341	361	374	1654	1654	1654	1654
42	1650	190	210	230	243	263	282	295	315	335	354	374	387	1706	1706	1706	1706
43	1700	190	210	230	243	263	282	295	315	335	354	374	387	1706	1706	1706	1706
44	1750	197	217	236	256	269	289	308	328	348	368	387	400	1759	1759	1759	1759
45	1800	197	217	236	256	269	289	308	328	348	368	387	400	1759	1759	1759	1759
46	1829	246	266	285	305	325	345	364	384	404	433	453	473	2048	2048	2048	2048
Table

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
F11:U46	Cell Value	=0	text	NO

Other sheet in same in work book with the required input conditions,

Work sheet.xlsx
	B	C	D	E	F
4	WIDTH (MM)	HEIGHT (MM)	TYPE	MODEL	Value
5
6	457	254	FD	IBD2	89
Work sheet

Cell Formulas
Range	Formula
F6	F6	=INDEX(INDIRECT(Table10[@MODEL]&"A"),MATCH(C6,INDIRECT(Table10[@MODEL]&"H"),0),MATCH(B6,INDIRECT(Table10[@MODEL]&"W"),0))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D6	Cell Value	=0	text	NO
E6	Cell Value	=0	text	NO
D6	Cell Value	=0	text	NO
E6	Cell Value	=0	text	NO
D5	Cell Value	=0	text	NO
E5	Cell Value	=0	text	NO
D5	Cell Value	=0	text	NO
E5	Cell Value	=0	text	NO
F6	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D5:D6	List	=TYPE
E5:E6	List	=INDIRECT(D5)

You would need to provide an XL2BB version of Table10 and the values in BC28 & BB28 and your expected result.
On the face of it the use of Indirect doesn't seem to add any value.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2
3
4		WIDTH (MM)	HEIGHT (MM)	TYPE	MODEL	Value
5
6		450	250	FD	IBD2	89
Test

Cell Formulas
Range	Formula
F6	F6	=INDEX(Table!$F$11:$U$46,XMATCH(C6,Table!E11:E46,1),XMATCH(B6,Table!F10:U10,1))

 

[IDRIS86]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F
1
2
3
4		WIDTH (MM)	HEIGHT (MM)	TYPE	MODEL	Value
5
6		450	250	FD	IBD2	89
Test

Cell Formulas
Range	Formula
F6	F6	=INDEX(Table!$F$11:$U$46,XMATCH(C6,Table!E11:E46,1),XMATCH(B6,Table!F10:U10,1))

Thanks. But Will this formula refer to the respective table by its name. Because I will have multiple tables at table sheet. Upon giving input such as Width , height & model, it needs to look at the respective table of that model and revert the values.

 

[Fluff]

No it will look at the specific range in the formula, because you gave no other information.

 

[IDRIS86]

No it will look at the specific range in the formula, because you gave no other information.

I just replaced MATCH function with your XMATCH function with Match 1 in my existing formula. It takes higher value as what I needed. It looks working.

=INDEX(INDIRECT(Table10[@MODEL]&"A"),XMATCH(D28,INDIRECT(Table10[@MODEL]&"H"),1 ),XMATCH(C28,INDIRECT(Table10[@MODEL]&"W"),1 ))

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[IDRIS86]

Glad you sorted it & thanks for the feedback.

Thanks a lot.

 

[IDRIS86]

Hi, Can you advise. The formula works. But if I copy the whole row and paste next line item. I couldnt find the issue. Could you advise.

Work sheet.xlsx
	B	C	D	E	F
4	WIDTH (MM)	HEIGHT (MM)	TYPE	MODEL	Value
5
6	530	508	FD	IBD2	135
7	530	508	FD	IBD2	#VALUE!
Work sheet

Cell Formulas
Range	Formula
F6:F7	F6	=INDEX(INDIRECT(Table10[@MODEL]&"A"),XMATCH(C6,INDIRECT(Table10[@MODEL]&"H"),1),XMATCH(B6,INDIRECT(Table10[@MODEL]&"W"),1))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D7	Cell Value	=0	text	NO
E7	Cell Value	=0	text	NO
D7	Cell Value	=0	text	NO
E7	Cell Value	=0	text	NO
F7	Cell Value	=0	text	NO
D6	Cell Value	=0	text	NO
E6	Cell Value	=0	text	NO
D6	Cell Value	=0	text	NO
E6	Cell Value	=0	text	NO
D5	Cell Value	=0	text	NO
E5	Cell Value	=0	text	NO
D5	Cell Value	=0	text	NO
E5	Cell Value	=0	text	NO
F6	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
D5:D7	List	=TYPE
E5:E7	List	=INDIRECT(D5)