Concat indirect address that has array of columns

[Dossfm0q]

Concat function is not working with indirect address that has array of columns

=CONCAT(INDIRECT(ADDRESS(1,{1,2,3})))
[/CO DE]

 

[Peter_SSs]

There is no indication of what is in A1:C1 and therefore what you are actually trying to achieve.
What about some sample a data and expected results with XL2BB?

.. and still no Excel version in profile so I wouldn't know what to advise anyway.

 

[Dossfm0q]

Profile was update excel 2019

Book1
	A	B	C	D	E
1	AA	BB	CCC		AA
2				To be	AABBCCC
Sheet1

Cell Formulas
Range	Formula
E1	E1	=CONCAT(INDIRECT(ADDRESS(1,{1,2,3})))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[jasonb75]

What is the reason behind using indirect and address instead of the simple version of =CONCAT(A1:C1) ?

 

[Dossfm0q]

Thanks for your response
this is to find a Excel 2016 formula as Mr: Peter Excel 365 formula in below Post
it ok with Address and not Ok when use it with Indirect(xxxx) not function

and I thank Mr: Peter for his initiative

concatenate based on column headers

Hi, Posting this after long time :) & Thanks for the help in advance. I have headers in A1 to Y1 like below a, b, c, d, "Employee", f,g,h etc... I want to concatenate values from each row and put in column Z for all rows , I would use formula like =Concatenate(A2,B2,C2,D2), however If the...

www.mrexcel.com

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		5	1		3			4			2		6			Ok	Not function
2	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	CONCATENATE Address	CONCATENATE Value
3	1	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	$C3$K3$E3$H3$B3$M3	ABC111
4	2	Active	ABC222	JFG2	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	$C4$K4$E4$H4$B4$M4	ABC222
5	3	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	$C5$K5$E5$H5$B5$M5	ABC111
6	4	Active	ABC222	JFG2	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	$C6$K6$E6$H6$B6$M6	ABC222
7	5	Active	ABC111	JFG1	Test2	Test3	Test4	Test5	Test6	Test7	Test8	Test9	Test10	Test11	Test12	$C7$K7$E7$H7$B7$M7	ABC111
Sheet1

 

[Fluff]

this is to find a Excel 2016 formula

Concat does not exist in 2016 version of Xl.

 

[Dossfm0q]

Sorry, I thought that it is a feature of Excel 2016 and 2019 both
and no mater, It is very nice from you to develop my Knowledge in Excel if you find a solution for 2019

 

[Fluff]

I wouldn't use a formula, I'd use a UDF as Peter did in the linked thread.

 

[jasonb75]

Without a proper explanation we have no idea what is actually needed, the formula in your example certainly doesn't appear to be doing anything useful, a much simpler formula,

=INDEX($B3:$O3,MATCH(MIN($B$1:$O$1),$B$1:$O$1,0))

will give the exact same results with a lot less effort.

As Fluff has just pointed out, the UDF that Peter suggested is likely the most practical and realistic solution.

 

[Peter_SSs]

Profile was update excel 2019

Thank you.

... if you find a solution for 2019

I don't have 2019 to test with but you could try this.

=CONCAT(INDEX(B3:O3,MATCH(ROW(INDEX(Q:Q,1):INDEX(Q:Q,COUNT(B$1:O$1))),B$1:O$1,0)))