INDEX, MATCH & INDIRECT- Still output comes for those horizontal & vertical ref not there in the table

[IDRIS86]

Hi,

There are two sheets in a single excel file. One is named worksheet & other named as value table.

In worksheet, I need the output values at G2, G3...upon the inputs C2 & D2. C2-Width & D2-Height.

C2 & D2 are referred into the Table DIBD60HA as shown below.

The issue is,

Output comes at G2, even the input sizes at C2 & D2 exceeds the maximum sizes given in the table. For example, if given inputs at width & height are 700 & 500 and no value available in the table. Because the maximum width reference in table is 600 only. The output value is 500. I'm not sure how this works. I would need to show the output to show as Not applicable or ref. It shouldn't show any values for out of table dimensions.

VALUE TABLE SHEET BELOW,

Work sheet 5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
5	SIZE	Width
6	Height				152	178	203	229	254	279	305	330	356	381	406	432	457	483	508	533	559	584	600
7					30	90	150	210	270	330	390	450	510	570	630	690	750	810	870	930	990	1050	1110
8					50	110	170	230	290	350	410	470	530	590	650	710	770	830	890	950	1010	1070	1130
9		150	30	60	90	120	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630
10		200	40	70	100	130	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640
11		250	50	80	110	140	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650
12		300	60	90	120	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660
13		350	70	100	130	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670
14		400	80	110	140	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680
15		450	90	120	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690
16		500	100	130	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670	700
17		550	110	140	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680	710
18		600	120	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690	720
19		650	130	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670	700	730
20		700	140	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680	710	740
21		750	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690	720	750
22		800	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670	700	730	760
23		850	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680	710	740	770
24		900	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690	720	750	780
25		950	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670	700	730	760	790
26		1000	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680	710	740	770	800
27		1050	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690	720	750	780	810
value table

Cell Formulas
Range	Formula
B10:B27	B10	=B9+50

WORKSHEET (SHEET NAME)

Work sheet 5.xlsx
	B	C	D	E	F	G
1	S. NO	WIDTH (MM)	HEIGHT (MM)	MODEL	TYPE	VALUE
2	1	700	500	DIBD60	H	500
3	2	203	1051	DIBD60	H	203
WORKSHEET

Cell Formulas
Range	Formula
G2:G3	G2	=(INDEX(INDIRECT(E2&F2&"A"),XMATCH(D2,INDIRECT(E2&F2&"H"),1 ),XMATCH(C2,INDIRECT(E2&F2&"W"),1 )))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3	Cell Value	=0	text	NO
G2	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E2:E3	List	=INDIRECT(#REF!)

 

[Fluff]

Try Putting a 0 in b7, b8, c6 & d6

 

[IDRIS86]

Try Putting a 0 in b7, b8, c6 & d6

Hi, But still it shows the same values after I included 0 in those cells,

Work sheet 5.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
5	SIZE	Width
6	Height		0	0	152	178	203	229	254	279	305	330	356	381	406	432	457	483	508	533	559	584	600
7		0			30	90	150	210	270	330	390	450	510	570	630	690	750	810	870	930	990	1050	1110
8		0			50	110	170	230	290	350	410	470	530	590	650	710	770	830	890	950	1010	1070	1130
9		150	30	60	90	120	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630
10		200	40	70	100	130	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640
11		250	50	80	110	140	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650
12		300	60	90	120	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660
13		350	70	100	130	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670
14		400	80	110	140	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680
15		450	90	120	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690
16		500	100	130	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670	700
17		550	110	140	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680	710
18		600	120	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690	720
19		650	130	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670	700	730
20		700	140	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680	710	740
21		750	150	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690	720	750
22		800	160	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670	700	730	760
23		850	170	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680	710	740	770
24		900	180	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690	720	750	780
25		950	190	220	250	280	310	340	370	400	430	460	490	520	550	580	610	640	670	700	730	760	790
26		1000	200	230	260	290	320	350	380	410	440	470	500	530	560	590	620	650	680	710	740	770	800
27		1050	210	240	270	300	330	360	390	420	450	480	510	540	570	600	630	660	690	720	750	780	810
value table

Cell Formulas
Range	Formula
B10:B27	B10	=B9+50

Work sheet 5.xlsx
	B	C	D	E	F	G
1	S. NO	WIDTH (MM)	HEIGHT (MM)	MODEL	TYPE	VALUE
2	1	700	500	DIBD60	H	500
3	2	203	1051	DIBD60	H	203
WORKSHEET

Cell Formulas
Range	Formula
G2:G3	G2	=(INDEX(INDIRECT(E2&F2&"A"),XMATCH(D2,INDIRECT(E2&F2&"H"),1 ),XMATCH(C2,INDIRECT(E2&F2&"W"),1 )))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G3	Cell Value	=0	text	NO
G2	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E2:E3	List	=INDIRECT(#REF!)

 

[Fluff]

What cells are your named ranges referring to?

 

[IDRIS86]

What cells are your named ranges referring to?

The table named as B6 to W27 - DIBD60HA
Cell B6 to B27 - DIBD60HH
Cell B6 to W6 - DIBD60HW

 

[Fluff]

In that case you will need to put a 0 in B6 as well.

 

[IDRIS86]

In that case you will need to put a 0 in B6 as well.

Hi, It works if I fill with 0 at B6 also.

Can you help with the another schedule, where my inputs at width & height are 3300 & 500. Width 3300 is not available in table range. But it shows 584. I'm not sure how it shows this value. I would need either 0 or N/a to be outcome.

Work sheet 6.xlsx
	B	C	D	E	F	G
1	S. NO	WIDTH (MM)	HEIGHT (MM)	MODEL	TYPE	VALUE
2	1	3300	500	FSD36	V	584
WORKSHEET

Cell Formulas
Range	Formula
G2	G2	=(INDEX(INDIRECT(E2&F2&"A"),XMATCH(D2,INDIRECT(E2&F2&"H"),1 ),XMATCH(C2,INDIRECT(E2&F2&"W"),1 )))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G2	Cell Value	=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
F2	List	=IF(NOT(ISERROR(MATCH(#REF!,List!$B$19:$B$21,0))),List!$J$19:$J$20,List!$J$21)

Work sheet 6.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
22			WIDTH
23		SIZE	203	254	305	356	406	457	508	559	610	660	711	762	813	864	914	950	2850	2900	2950	3000	3050	3100	3150	3200
24	HEIGHT	178	20	40	60	80	100	120	140	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480
25		279	30	50	70	90	110	130	150	170	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490
26		381	40	60	80	100	120	140	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500
27		483	50	70	90	110	130	150	170	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510
28		584	60	80	100	120	140	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520
29		686	70	90	110	130	150	170	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510	530
30		787	80	100	120	140	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540
31		889	90	110	130	150	170	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510	530	550
32		991	100	120	140	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540	560
33		2550	110	130	150	170	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510	530	550	570
34		2600	120	140	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540	560	580
35		2650	130	150	170	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510	530	550	570	590
36		2700	140	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540	560	580	600
37		2750	150	170	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510	530	550	570	590	610
38		2800	160	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540	560	580	600	620
39		2850	170	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510	530	550	570	590	610	630
40		2900	180	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540	560	580	600	620	640
41		2950	190	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510	530	550	570	590	610	630	650
42		3000	200	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540	560	580	600	620	640	660
43		3050	210	230	250	270	290	310	330	350	370	390	410	430	450	470	490	510	530	550	570	590	610	630	650	670
44		3099	220	240	260	280	300	320	340	360	380	400	420	440	460	480	500	520	540	560	580	600	620	640	660	680
VALUES

Cell Formulas
Range	Formula
D24:Z44	D24	=C24+20
C25:C44	C25	=C24+10

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E26:Z44	Cell Value	=0	text	NO
E24:Z25	Cell Value	=0	text	NO
D26:D44	Cell Value	=0	text	NO
C26:C44	Cell Value	=0	text	NO
C24:D25	Cell Value	=0	text	NO

 

[Fluff]

Try putting a 0 in B23 in place of Size

 

[IDRIS86]

Try putting a 0 in B23 in place of Size

Hi It works. Thanks. May I know the reason if you could explain for me to understand.

 

[Fluff]

Its because of the way you have the tables laid out.
For your original data the ranges should really be
DIBD60HA C7:W27
DIBD60HH B7:B27
DIBD60HW C6:W6