Multiple text to add in INDIRECT

[IDRIS86]

Hi,

I have my existing formula like the below,

=(INDEX(INDIRECT(O29&"A"),XMATCH(D29,INDIRECT(O29&"H"),1 ),XMATCH(C29,INDIRECT(O29&"W"),1 )))

Let say I have text in cell O29 is FSD & I will have Q28 cell as either H or V. If it is H, I wanted INDIRECT to create reference like FSDHA.
I'm not sure how to add that Q28 cell reference in INDIRECT after O29. Please assist.

 

[jasonb75]

Perhaps INDIRECT(O29&Q28&"A")

 

[IDRIS86]

Perhaps INDIRECT(O29&Q28&"A")

It doesn't work. I tried that.

Work sheet 4.xlsx
	C	D	E	F	G
1	WIDTH (MM)	HEIGHT (MM)	Type	Way	DAMPER
2	305	254	IBD2	V	#REF!
WORKSHEET

Cell Formulas
Range	Formula
G2	G2	=(INDEX(INDIRECT(E2&"A"),XMATCH(D2,INDIRECT(E2&"H"),1 ),XMATCH(C2,INDIRECT(E2&"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
E2	List	=INDIRECT(#REF!)
F2	List	=IF(NOT(ISERROR(MATCH(#REF!,#REF!$B$5:$B$8,0))),#REF!$J$19:$J$20,#REF!$J$21)

Work sheet 4.xlsx
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
8	SIZE	102	152	203	254	305	356	2400	2450	2500	2550	2600	2650	2700	2750	2800	2850	2900	2950	3000	3048
9	102	53	59	62	66	69	72	325	345	345	394	394	407	407	407	407	420	420	420	420	420
10	152	56	59	62	66	72	75	335	345	345	394	394	420	420	420	420	433	433	433	433	433
11	203	59	62	66	72	75	79	354	354	354	420	420	446	446	446	446	459	459	459	459	459
12	254	59	66	69	72	75	82	364	354	354	433	433	446	446	446	446	473	473	473	473	473
13	305	62	69	72	79	82	89	394	394	394	459	459	486	486	486	486	512	512	512	512	512
14	356	66	72	75	82	85	92	413	413	413	499	499	512	512	512	512	538	538	538	538	538
15	406	69	75	79	85	92	95	443	443	443	525	525	551	551	551	551	564	564	564	564	564
16	457	72	79	85	92	95	102	473	473	473	551	551	578	578	578	578	604	604	604	604	604
17	508	75	82	89	95	102	108	502	502	502	591	591	617	617	617	617	643	643	643	643	643
18	559	79	85	92	98	105	112	522	532	532	617	617	643	643	643	643	669	669	669	669	669
19	1650	190	210	230	243	263	282	1339	1358	1358	1549	1549	1628	1628	1628	1628	1706	1706	1706	1706	1706
20	1700	190	210	230	243	263	282	1339	1358	1358	1549	1549	1628	1628	1628	1628	1706	1706	1706	1706	1706
21	1750	197	217	236	256	269	289	1378	1398	1398	1601	1601	1706	1706	1706	1706	1759	1759	1759	1759	1759
22	1800	197	217	236	256	269	289	1378	1398	1398	1601	1601	1706	1706	1706	1706	1759	1759	1759	1759	1759
23	1829	246	266	285	305	325	345	1595	1624	1624	1890	1890	1969	1969	1969	1969	2048	2048	2048	2048	2048
List

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

 

[jasonb75]

Try telling us what you're trying to to instead of how you're trying to do it. I had assumed from your example formula that you were using INDIRECT to identify a named range, but there are no named ranges shown in your sheet.

 

[IDRIS86]

There are two sheets in a single excel file. One is named as List and other is worksheet.

Upon entering the inputs at C2, D2, E2 & F2 in worksheet the output should come at G2.

The table in List sheet is named as IBD2VA. Its vertical sizes in rows are height named as IBDVH & sizes given horizontally in columns are width named as IBD2VW.
So by the formula of indirect reference, it needs to go the table IBD2VA and pull the value upon matching horizontal & vertical matching references. Xmatch is used to pick higher values if given width and height is not available in the table.

The formula works better. But I want the formula to create a reference with E2 & F2 and additional string I have added "A". So if it creates the reference IBD2 from E2, V from F2 and string "A" together as IBD2VA. So then it can refer to the respective table named as IBD2VA.

I need to include F2 into the Indirect reference in the below formula, between E2 and A.

=(INDEX(INDIRECT(E2&"A"),XMATCH(D2,INDIRECT(E2&"H"),1 ),XMATCH(C2,INDIRECT(E2&"W"),1 )))


WORKSHEET

Work sheet 4.xlsx
	B	C	D	E	F	G
1	S. NO	WIDTH (MM)	HEIGHT (MM)	Type	Way	Value
2	1	305	254	IBD2	V	#REF!
3
4
5
WORKSHEET

Cell Formulas
Range	Formula
G2	G2	=(INDEX(INDIRECT(E2&"A"),XMATCH(D2,INDIRECT(E2&"H"),1 ),XMATCH(C2,INDIRECT(E2&"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
E2	List	=INDIRECT(#REF!)
F2	List	=IF(NOT(ISERROR(MATCH(#REF!,#REF!,0))),#REF!,#REF!)

LIST

Work sheet 4.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
4	WIDTH
5	HEIGHT	SIZE	102	152	203	254	305	356	2400	2450	2500	2550	2600	2650	2700	2750	2800	2850	2900	2950	3000	3048
6		102	53	59	62	66	69	72	325	345	345	394	394	407	407	407	407	420	420	420	420	420
7		152	56	59	62	66	72	75	335	345	345	394	394	420	420	420	420	433	433	433	433	433
8		203	59	62	66	72	75	79	354	354	354	420	420	446	446	446	446	459	459	459	459	459
9		254	59	66	69	72	75	82	364	354	354	433	433	446	446	446	446	473	473	473	473	473
10		305	62	69	72	79	82	89	394	394	394	459	459	486	486	486	486	512	512	512	512	512
11		356	66	72	75	82	85	92	413	413	413	499	499	512	512	512	512	538	538	538	538	538
12		406	69	75	79	85	92	95	443	443	443	525	525	551	551	551	551	564	564	564	564	564
13		457	72	79	85	92	95	102	473	473	473	551	551	578	578	578	578	604	604	604	604	604
14		508	75	82	89	95	102	108	502	502	502	591	591	617	617	617	617	643	643	643	643	643
15		559	79	85	92	98	105	112	522	532	532	617	617	643	643	643	643	669	669	669	669	669
16		1650	190	210	230	243	263	282	1339	1358	1358	1549	1549	1628	1628	1628	1628	1706	1706	1706	1706	1706
17		1700	190	210	230	243	263	282	1339	1358	1358	1549	1549	1628	1628	1628	1628	1706	1706	1706	1706	1706
18		1750	197	217	236	256	269	289	1378	1398	1398	1601	1601	1706	1706	1706	1706	1759	1759	1759	1759	1759
19		1800	197	217	236	256	269	289	1378	1398	1398	1601	1601	1706	1706	1706	1706	1759	1759	1759	1759	1759
20		1829	246	266	285	305	325	345	1595	1624	1624	1890	1890	1969	1969	1969	1969	2048	2048	2048	2048	2048
List

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

 

[jasonb75]

Based on the additional information,

=(INDEX(INDIRECT(E2&F2&"A"),XMATCH(D2,INDIRECT(E2&F2&"H"),1 ),XMATCH(C2,INDIRECT(E2&F2&"W"),1)))

which is much the same as my first suggestion, with the exception that I only mentioned the first range previously rather than all 3 ranges.

If that doesn't work then I would suspect an error with the named range definitions.

 

[IDRIS86]

Based on the additional information,

=(INDEX(INDIRECT(E2&F2&"A"),XMATCH(D2,INDIRECT(E2&F2&"H"),1 ),XMATCH(C2,INDIRECT(E2&F2&"W"),1)))

which is much the same as my first suggestion, with the exception that I only mentioned the first range previously rather than all 3 ranges.

If that doesn't work then I would suspect an error with the named range definitions.

Hi you are correct. I tried initially just inlcuded F2 at the very first indirect function only. Should have included the same in the next two indirect functions, which refers the table index. Thanks