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))

 

[IDRIS86]

If you are happy to rename your table as IBD2A, then you should be able to just copy the F6 formula to your F6

But I already named the content as IBD2A already. Sizes in rows are named as IBD2H & the ones in column as IBD2W. Please refer to the snapshot I attached, shows the table name.

 

[Alex Blakenburg]

I am assuming the use of Excel Tables.
I suspect you are using named ranges.

 

[IDRIS86]

I am assuming the use of Excel Tables.
I suspect you are using named ranges.

View attachment 34885

I tried, it doesn't show me the values. Also will this work if I have different models and each have their tables?. So it automatically refers to the specific table upon the model in worksheet.

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

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

Cell Formulas
Range	Formula
F6:F7	F6	=INDEX(INDIRECT(E6&"A"&"[#All]"),XMATCH(VALUE(B6),INDIRECT(E6&"A"&"[[#All],[SIZE]]"),1),XMATCH(TRIM(C6),INDIRECT(E6&"A"&"[#Headers]"),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
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

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

 

[Alex Blakenburg]

It will work as long as you use excel tables with a table name = model name & A.
And you don't have spaces before & after the name in your work sheet model column.

If you already have a rangename of IBD2A you will need to delete that before trying to rename the table.
It's 2am here so I am login off now.

 

[Alex Blakenburg]

Did you work out how to create and Excel table and name it IBD2A and get the formula to work ?
You can of course name is something else like IBD2X if you don't want to delete your range name but you would need to change the formula from & "A" to & "X".

 

[IDRIS86]

Did you work out how to create and Excel table and name it IBD2A and get the formula to work ?
You can of course name is something else like IBD2X if you don't want to delete your range name but you would need to change the formula from & "A" to & "X".

Hi I tried with my same formula by removing the table format in the worksheet input page and when I tried copied down the drop down rows. it works well.

But I have another issue. In the same work sheet, I needed other values from some other table. I used the same formula which worked out before for me as below.
I included an IF condition in addition.
The issue is it pulls the values at higher look up values. Let say the inputs width is 420 & height is 212. the database table has next higher lookup values Width 406 and next higher is 508. For height is 203 and next higher is 305. I needed the formula to pull the values of Width 508 and height 305. which is 61. But it pulls 66, which could be the value of next higher sizes.
I highlighted in table. Please assist.

=IF(E4="YES",INDEX(INDIRECT($E$3&"A"),XMATCH(D4,INDIRECT($E$3&"H"),1 ),XMATCH(C4,INDIRECT($E$3&"W"),1 )),0)

Work sheet 2.xlsx
	B	C	D	E	F	G
3	S. NO	WIDTH (MM)	HEIGHT (MM)	SLEEVE	SLEEVE TYPE	SLEEVE
4	1	420	212	YES	24 IN 16 GA	66
INPUT

Cell Formulas
Range	Formula
G4	G4	=IF(E4="YES",INDEX(INDIRECT($E$3&"A"),XMATCH(D4,INDIRECT($E$3&"H"),1 ),XMATCH(C4,INDIRECT($E$3&"W"),1 )),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4	Cell Value	contains "NO"	text	NO
G4	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E4	List	=List!$T$5:$T$6
F4	List	=LENGTHGAUGE

Work sheet 2.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
14	SIZE	102	203	305	406	508	610	711	813	914	1016	1118	1219	1321	1422	6950	7000	7050	7100	7150	7200	7250	7300	7350
15	102	26	33	39	44	50	55	69	75	80	86	92	98	108	113	515	515	531	531	531	531	531	531	568
16	203	33	39	44	50	55	61	75	80	86	92	98	103	113	120	531	531	548	548	548	548	548	548	584
17	305	39	44	50	55	61	66	80	86	92	98	103	109	120	125	548	548	564	564	564	564	564	564	601
18	406	44	50	55	61	66	72	86	92	98	103	109	114	125	131	564	564	581	581	581	581	581	581	617
19	508	50	55	61	66	72	77	92	98	103	109	114	120	131	136	581	581	597	597	597	597	597	597	637
20	610	55	61	66	72	77	84	98	103	109	114	120	125	136	142	597	597	614	614	614	614	614	614	653
21	711	69	75	80	86	92	98	118	123	129	134	140	145	160	165	680	680	696	696	696	696	696	696	749
22	813	75	80	86	92	98	103	123	129	134	140	145	152	165	171	696	696	716	716	716	716	716	716	766
23	914	80	86	92	98	103	109	129	134	140	145	152	157	171	176	716	716	733	733	733	733	733	733	782
24	1016	86	92	98	103	109	114	134	140	145	152	157	163	176	182	733	733	749	749	749	749	749	749	799
25	1118	92	98	103	109	114	120	140	145	152	157	163	168	182	188	749	749	766	766	766	766	766	766	815
26	1219	98	103	109	114	120	125	145	152	157	163	168	174	188	194	766	766	782	782	782	782	782	782	832
27	4700	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
28	4750	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
29	4800	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
30	4850	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
31	4900	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
32	4950	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
33	5000	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
Sheet 1

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

 

[Alex Blakenburg]

Thanks for including all the information, step 1 is generally trying to replicate what you are doing and having all the XL2BB data really helps.
I am assuming your data " YES "& " 24 IN 16 GA " doesn't have the leading and trailing spaces I am getting because you definitely don't want them
(if you have anything different in those cells let me know because it would be helpful to know what is causing the additional spaces since its not happening in the actual column headings)

Your formula looks fine. What I can't see your range names and I suspect the ranges are out of sync.
Match gives you a count from the left or top starting at 1, therefore the starting point for the 2 x-y (width-height) match ranges needs to be the same as your target area.
With your table's top left corner being E14, the simplest formula would be that SLEEVEW, SLEEVEH, SLEEVEA, all start at E14.
(yes you could focus on just the data range but then all 3 range names would need to start at a different cell reference, so why make it difficult for yourself)

Let me know how you go.

PS: the one thing I was missing that you might want to display visually on your charts is which dimension is which (ie Width vs Height).

 

[IDRIS86]

Thanks for including all the information, step 1 is generally trying to replicate what you are doing and having all the XL2BB data really helps.
I am assuming your data " YES "& " 24 IN 16 GA " doesn't have the leading and trailing spaces I am getting because you definitely don't want them
(if you have anything different in those cells let me know because it would be helpful to know what is causing the additional spaces since its not happening in the actual column headings)

Your formula looks fine. What I can't see your range names and I suspect the ranges are out of sync.
Match gives you a count from the left or top starting at 1, therefore the starting point for the 2 x-y (width-height) match ranges needs to be the same as your target area.
With your table's top left corner being E14, the simplest formula would be that SLEEVEW, SLEEVEH, SLEEVEA, all start at E14.
(yes you could focus on just the data range but then all 3 range names would need to start at a different cell reference, so why make it difficult for yourself)

Let me know how you go.

PS: the one thing I was missing that you might want to display visually on your charts is which dimension is which (ie Width vs Height).

Hi, I haven't considered leading & trailing spaces at text "YES". To obtain this value "24 IN 16GA" is not a dependent. So I removed it from the table.
I just applied the same formula which worked out for me the tables we discussed before. I'm not aware about other formula which could help me on my requirement.
I highlighted in the table now. Width is in green color & Height in blue. If there is any other way with some formula. My requirement is upon selecting YES in the input condition, the formula to pick values from the table with reference to given width & height inputs. If give size not available, it needs to pick the value of next immediate higher width & height. please assist.

Work sheet 2.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
14	SIZE	102	203	305	406	508	610	711	813	914	1016	1118	1219	1321	1422	6950	7000	7050	7100	7150	7200	7250	7300	7350
15	102	26	33	39	44	50	55	69	75	80	86	92	98	108	113	515	515	531	531	531	531	531	531	568
16	203	33	39	44	50	55	61	75	80	86	92	98	103	113	120	531	531	548	548	548	548	548	548	584
17	305	39	44	50	55	61	66	80	86	92	98	103	109	120	125	548	548	564	564	564	564	564	564	601
18	406	44	50	55	61	66	72	86	92	98	103	109	114	125	131	564	564	581	581	581	581	581	581	617
19	508	50	55	61	66	72	77	92	98	103	109	114	120	131	136	581	581	597	597	597	597	597	597	637
20	610	55	61	66	72	77	84	98	103	109	114	120	125	136	142	597	597	614	614	614	614	614	614	653
21	711	69	75	80	86	92	98	118	123	129	134	140	145	160	165	680	680	696	696	696	696	696	696	749
22	813	75	80	86	92	98	103	123	129	134	140	145	152	165	171	696	696	716	716	716	716	716	716	766
23	914	80	86	92	98	103	109	129	134	140	145	152	157	171	176	716	716	733	733	733	733	733	733	782
24	1016	86	92	98	103	109	114	134	140	145	152	157	163	176	182	733	733	749	749	749	749	749	749	799
25	1118	92	98	103	109	114	120	140	145	152	157	163	168	182	188	749	749	766	766	766	766	766	766	815
26	1219	98	103	109	114	120	125	145	152	157	163	168	174	188	194	766	766	782	782	782	782	782	782	832
27	4700	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
28	4750	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
29	4800	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
30	4850	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
31	4900	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
32	4950	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
33	5000	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
Sheet 1

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

Work sheet 2.xlsx
	B	C	D	E	F
3	S. NO	WIDTH (MM)	HEIGHT (MM)	SLEEVE	SLEEVE
4	1	420	212	YES	66
INPUT

Cell Formulas
Range	Formula
F4	F4	=IF(E4="YES",INDEX(INDIRECT($E$3&"A"),XMATCH(D4,INDIRECT($E$3&"H"),1 ),XMATCH(C4,INDIRECT($E$3&"W"),1 )),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4	Cell Value	contains "NO"	text	NO
F4	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E4	List	=List!$T$5:$T$6

 

[Alex Blakenburg]

I do understand what you are saying but I believe your issue is not caused by the formula but by the range names.

Change your range names to match mine and I think you will find that it works.
(Obviously the sheet name part will be different)

 

[IDRIS86]

I do understand what you are saying but I believe your issue is not caused by the formula but by the range names.

Change your range names to match mine and I think you will find that it works.
(Obviously the sheet name part will be different)

View attachment 34969

I tried to change SLEEVEA also with E14 and it picks lesser value than required.

Work sheet 2.xlsx
	B	C	D	E	F
3	S. NO	WIDTH (MM)	HEIGHT (MM)	SLEEVE	SLEEVE
4	1	420	212	YES	55
INPUT

Cell Formulas
Range	Formula
F4	F4	=IF(E4="YES",INDEX(INDIRECT($E$3&"A"),XMATCH(D4,INDIRECT($E$3&"H"),1 ),XMATCH(C4,INDIRECT($E$3&"W"),1 )),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E4	Cell Value	contains "NO"	text	NO
F4	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E4	List	=List!$T$5:$T$6

Work sheet 2.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
14	SIZE	102	203	305	406	508	610	711	813	914	1016	1118	1219	1321	1422	6950	7000	7050	7100	7150	7200	7250	7300	7350
15	102	26	33	39	44	50	55	69	75	80	86	92	98	108	113	515	515	531	531	531	531	531	531	568
16	203	33	39	44	50	55	61	75	80	86	92	98	103	113	120	531	531	548	548	548	548	548	548	584
17	305	39	44	50	55	61	66	80	86	92	98	103	109	120	125	548	548	564	564	564	564	564	564	601
18	406	44	50	55	61	66	72	86	92	98	103	109	114	125	131	564	564	581	581	581	581	581	581	617
19	508	50	55	61	66	72	77	92	98	103	109	114	120	131	136	581	581	597	597	597	597	597	597	637
20	610	55	61	66	72	77	84	98	103	109	114	120	125	136	142	597	597	614	614	614	614	614	614	653
21	711	69	75	80	86	92	98	118	123	129	134	140	145	160	165	680	680	696	696	696	696	696	696	749
22	813	75	80	86	92	98	103	123	129	134	140	145	152	165	171	696	696	716	716	716	716	716	716	766
23	914	80	86	92	98	103	109	129	134	140	145	152	157	171	176	716	716	733	733	733	733	733	733	782
24	1016	86	92	98	103	109	114	134	140	145	152	157	163	176	182	733	733	749	749	749	749	749	749	799
25	1118	92	98	103	109	114	120	140	145	152	157	163	168	182	188	749	749	766	766	766	766	766	766	815
26	1219	98	103	109	114	120	125	145	152	157	163	168	174	188	194	766	766	782	782	782	782	782	782	832
27	4700	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
28	4750	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
29	4800	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
30	4850	376	393	409	426	442	459	528	545	564	581	597	614	660	677	2614	2614	2663	2663	2663	2663	2663	2663	2831
31	4900	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
32	4950	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
33	5000	393	409	426	442	459	475	545	564	581	597	614	630	677	693	2663	2663	2713	2713	2713	2713	2713	2713	2881
Sheet 1

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