Multiple text to add in INDIRECT

IDRIS86

Board Regular
Joined
Mar 18, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,227
Office Version
  1. 365
Platform
  1. Windows
Perhaps INDIRECT(O29&Q28&"A")
 

IDRIS86

Board Regular
Joined
Mar 18, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Perhaps INDIRECT(O29&Q28&"A")
It doesn't work. I tried that.


Work sheet 4.xlsx
CDEFG
1WIDTH (MM)HEIGHT (MM)TypeWayDAMPER
2305254 IBD2 V #REF!
WORKSHEET
Cell Formulas
RangeFormula
G2G2=(INDEX(INDIRECT(E2&"A"),XMATCH(D2,INDIRECT(E2&"H"),1 ),XMATCH(C2,INDIRECT(E2&"W"),1 )))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
E2List=INDIRECT(#REF!)
F2List=IF(NOT(ISERROR(MATCH(#REF!,#REF!$B$5:$B$8,0))),#REF!$J$19:$J$20,#REF!$J$21)



Work sheet 4.xlsx
EFGHIJKLMNOPQRSTUVWXY
8SIZE10215220325430535624002450250025502600265027002750280028502900295030003048
9102535962666972325345345394394407407407407420420420420420
10152565962667275335345345394394420420420420433433433433433
11203596266727579354354354420420446446446446459459459459459
12254596669727582364354354433433446446446446473473473473473
13305626972798289394394394459459486486486486512512512512512
14356667275828592413413413499499512512512512538538538538538
15406697579859295443443443525525551551551551564564564564564
164577279859295102473473473551551578578578578604604604604604
1750875828995102108502502502591591617617617617643643643643643
1855979859298105112522532532617617643643643643669669669669669
19165019021023024326328213391358135815491549162816281628162817061706170617061706
20170019021023024326328213391358135815491549162816281628162817061706170617061706
21175019721723625626928913781398139816011601170617061706170617591759175917591759
22180019721723625626928913781398139816011601170617061706170617591759175917591759
23182924626628530532534515951624162418901890196919691969196920482048204820482048
List
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F9:Y23Cell Value=0textNO
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,227
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 18, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
BCDEFG
1S. NOWIDTH (MM)HEIGHT (MM)TypeWayValue
21305254 IBD2 V #REF!
3
4
5
WORKSHEET
Cell Formulas
RangeFormula
G2G2=(INDEX(INDIRECT(E2&"A"),XMATCH(D2,INDIRECT(E2&"H"),1 ),XMATCH(C2,INDIRECT(E2&"W"),1 )))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
G2Cell Value=0textNO
Cells with Data Validation
CellAllowCriteria
E2List=INDIRECT(#REF!)
F2List=IF(NOT(ISERROR(MATCH(#REF!,#REF!,0))),#REF!,#REF!)


LIST

Work sheet 4.xlsx
ABCDEFGHIJKLMNOPQRSTUV
4WIDTH
5HEIGHTSIZE10215220325430535624002450250025502600265027002750280028502900295030003048
6102535962666972325345345394394407407407407420420420420420
7152565962667275335345345394394420420420420433433433433433
8203596266727579354354354420420446446446446459459459459459
9254596669727582364354354433433446446446446473473473473473
10305626972798289394394394459459486486486486512512512512512
11356667275828592413413413499499512512512512538538538538538
12406697579859295443443443525525551551551551564564564564564
134577279859295102473473473551551578578578578604604604604604
1450875828995102108502502502591591617617617617643643643643643
1555979859298105112522532532617617643643643643669669669669669
16165019021023024326328213391358135815491549162816281628162817061706170617061706
17170019021023024326328213391358135815491549162816281628162817061706170617061706
18175019721723625626928913781398139816011601170617061706170617591759175917591759
19180019721723625626928913781398139816011601170617061706170617591759175917591759
20182924626628530532534515951624162418901890196919691969196920482048204820482048
List
Cells with Conditional Formatting
CellConditionCell FormatStop If True
C6:V20Cell Value=0textNO
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,227
Office Version
  1. 365
Platform
  1. Windows
Based on the additional information,
Excel Formula:
=(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.
 
Solution

IDRIS86

Board Regular
Joined
Mar 18, 2021
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Based on the additional information,
Excel Formula:
=(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
 

Forum statistics

Threads
1,137,124
Messages
5,679,743
Members
419,854
Latest member
marvin24

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top