concat

[zoharb]

	2014	2015	2016	2017	2018	2019
aug	5	7	9	11	13	15
sep	9	10	12	15	17	19
oct	13	13	15	19	21	23
nov	17	16	18	23	25	27
dec	21	19	21	27	29	31
jan	25	27	29	31	33	35
		35	31
		jan 2019	dec 2019jan 2017

want to get multiple results as in D61 in horizontal

 

[dermie_72]

you could use this code and run this macro in the cell below the lookup value. Adjust your range and settings to suit.

Sub multijoiner()
Dim rng As Range
Dim A As String
Dim B As Integer, I As Integer, C As Integer

Set rng = Range("A1:G7") ' Adjust as required
A = ""
B = ActiveCell.Offset(-1, 0).Value
C = Application.WorksheetFunction.CountIfs(rng, B)
If C = 0 Then
    ActiveCell = "Not found"
    Set rng = Nothing
    Exit Sub
    Else
End If
For Each Cell In rng
    If Cell.Value = B Then
        A = A & Cells(Cell.Row(), 1).Value & " " & Cells(1, Cell.Column()).Value
        Else
    End If
Next
ActiveCell = A
Set rng = Nothing
End Sub

 

[Peter_SSs]

When using XL2BB it is normally best to use 'Mini Sheet' instead of 'Table Only' so we can see just where your data is. For example, in post 1 you referred to cell D61 but we have no idea from your XL2BB (or image) what is in that cell.

Something like this perhaps?
Column J if you want to copy the formula down or column K if you want a single formula to spill all the results.

25 04 01.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1		2014	2015	2016	2017	2018	2019		35	jan 2019	jan 2019
2	aug	5	7	9	11	13	15		31	dec 2019, jan 2017	dec 2019, jan 2017
3	sep	9	10	12	15	17	19
4	oct	13	13	15	19	21	23
5	nov	17	16	18	23	25	27
6	dec	21	19	21	27	29	31
7	jan	25	27	29	31	33	35
zoharb

Cell Formulas
Range	Formula
K1:K2	K1	=MAP(I1:I2,LAMBDA(r,TEXTJOIN(", ",1,IF(B2:G7=r,A2:A7&" "&B1:G1,""))))
J1:J2	J1	=TEXTJOIN(", ",1,IF(B$2:G$7=I1,A$2:A$7&" "&B$1:G$1,""))
Dynamic array formulas.

 

[zoharb]

1apr25-invoice-all clients-Sep23.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		2014	2015	2016	2017	2018	2019
2	aug	5	7	9	11	13	15	27	nov 2019dec 2017jan 2015
3	sep	9	10	12	15	17	19	27	nov 2019, dec 2017, jan 2015
4	oct	13	13	15	19	21	23				output @now
5	nov	17	16	18	23	25	27	27	Nov-19		output needed
6	dec	21	19	21	27	29	31		Dec-17
7	jan	25	27	29	31	33	35		Jan-15
Sheet2

Cell Formulas
Range	Formula
I2	I2	=CONCAT((IF(H2=B2:G7,A2:A7&" "&B1:G1,"")))
I3	I3	=TEXTJOIN(", ",1,IF(B$2:G$7=H3,A$2:A$7&" "&B$1:G$1,""))

Respected ,
I need output of each data in a different row. this is needed as we have space constraints in the place where I want to apply

 

[Peter_SSs]

I need output of each data in a different row.

Good idea to tell/show us that at the start?

Here is one way.

25 04 01.xlsm
	A	B	C	D	E	F	G	H	I
1		2014	2015	2016	2017	2018	2019
2	aug	5	7	9	11	13	15
3	sep	9	10	12	15	17	19
4	oct	13	13	15	19	21	23
5	nov	17	16	18	23	25	27	27	Nov-19
6	dec	21	19	21	27	29	31		Dec-17
7	jan	25	27	29	31	33	35		Jan-15
zoharb (2)

Cell Formulas
Range	Formula
I5:I7	I5	=FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,IF(B2:G7=H5,A2:A7&"-"&B1:G1,""))&"</c></p>","//c")
Dynamic array formulas.

 

[PeteWright]

I've tried to solve this with the FILTER function and ran into an error which I can't explain.

MrExcel_2025-03.xlsm
	A	B	C	D	E	F	G
1		2014	2015	2016	2017	2018	2019
2	aug	5	7	9	11	13	15
3	sep	9	10	12	15	17	19
4	oct	13	13	15	19	21	23
5	nov	17	16	18	23	25	27
6	dec	21	19	21	27	29	31
7	jan	25	27	29	31	33	35
8			35	31
9			#VALUE!
013

Cell Formulas
Range	Formula
C9	C9	=FILTER(B2:G7,B2:G7=C8,"")

Why is this throwing the #VALUE! error? As far as I understood the function syntax it shouldn't happen. What am I missing?

EDIT: Changing the references from relative to absolute doesn't work either. In any way.

 

[Peter_SSs]

Why is this throwing the #VALUE! error?

It looks like you are trying to treat the 6 x 6 array of cells as if it were a 1 column x 36 rows array. Filtering the array B2:G7 would always return an array containing 6 columns.

I'm not exactly sure what you were trying to do with that formula as it has nothing to so with column A or row 1 that the OP wants, but with 365 (but not 2021 that the OP has) you could do something like ..
=LET(d,TOCOL(B2:G7),FILTER(d,d=C8,""))
.. but all that would do is return the C8 value repeated as many times as that value occurs in the given range.