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]

Send me a screenshot of your name manager, make sure I can see the full RefersTo content.

My results:

20210320 Index Match Multiple Tables.xlsx
	A	B	C	D	E	F	G	H
1
2
3		S. NO	WIDTH (MM)	HEIGHT (MM)	SLEEVE	SLEEVE TYPE	SLEEVE
4		1	420	212	YES	24 IN 16 GA	60.5
5		1	420	212	YES	24 IN 16 GA	60.5
6
INPUT

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

 

[IDRIS86]

Hi, I attached. Please check.

 

[Alex Blakenburg]

Your SLEEVEW starts at $F$14 it needs to start at $E$14.

 

[IDRIS86]

Your SLEEVEW starts at $F$14 it needs to start at $E$14.

Great!. It works! Thank you.

But I don't know why the earlier table works with my same type of formula. Please check the below, it picks the correct value I needed. Also I attached its name manager screenshot.

Work sheet.xlsx
	B	C	D	E	F
4	WIDTH (MM)	HEIGHT (MM)	TYPE	MODEL	Value
5	458	509	FD	IBD2	131
Work sheet

Cell Formulas
Range	Formula
F5	F5	=INDEX(INDIRECT(Table10[@MODEL]&"A"),XMATCH(C5,INDIRECT(Table10[@MODEL]&"H"),1),XMATCH(B5,INDIRECT(Table10[@MODEL]&"W"),1))

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

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

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

 

[Alex Blakenburg]

The range names you have done for the Model (Work Sheet) are fine, its just that its the harder method, which is attested to by the fact that you didn't get it quite right when you tried to do it for the Sleeve (Input) work sheet.

I've tried to show a comparison of the 2 methods below, not sure how successfully.

So looking at the way you did it for the sleeve version.
W is determining the Columns for A so the columns have to line up.
H is determining the Rows for A so the Rows have to line up

 

[IDRIS86]

The range names you have done for the Model (Work Sheet) are fine, its just that its the harder method, which is attested to by the fact that you didn't get it quite right when you tried to do it for the Sleeve (Input) work sheet.

I've tried to show a comparison of the 2 methods below, not sure how successfully.

View attachment 34985

So looking at the way you did it for the sleeve version.
W is determining the Columns for A so the columns have to line up.
H is determining the Rows for A so the Rows have to line up

View attachment 34986

I understood the difference between 2 with your good pictorial presentation. I referred this formula in youtube and applied the same not to have any errors. For IBD2, it works well and when I applied the same for SLEEVE, it becomes incorrect. Not sure why it happens. In your last table I understand differences. But may I know what is MATCH under column and Row you specified.

Also can you explain what this means
W is determining the Columns for A so the columns have to line up.
H is determining the Rows for A so the Rows have to line up

Thanks a lot for your support & time.

 

[Alex Blakenburg]

The pictures, the column / row match & the text are 3 different ways of saying the same thing. If one works for you just ignore the other 2.

IBD2W is the heading row. You are using a match on that to find the column. If it finds a column and for example its column 3, then if IBD2W starts at F then what you want is F + 3 columns = H.
BUT its not adding the 3 to where IBD2W start its applying it to the IBD2A range. So IBD2A needs to start at the same Column in this case Column F so that F + 3 columns = H.
If the IBD2A range has a different starting point say E then E +3 Columns = G but you needed H.
If my count looks wrong its because the + 3 is not strictly correct, its an index no, so F would be 1, G would be 2, H would be 3.

The same holds true for Rows IBD2H after a match will return a number, this number is based on where the range starts. If when you apply it to IBD2A it starts on a different row, then when you use the number your match returned and apply it to a different starting point, you will be at a different row to the intended one.

I might have confused you further. That's why I think its much easier to use the top left corner for all the references then you know that they all start at the same place and that the column and row counters are all going to line up.