Concatenate formula

I'm trying to get a formula using concatenate that will identify numbers in a
row 3 (4&5) and returning the name of the column above into one cell.

Big small large larger
1 4 5 2

So in one cell I should get [small, large]
I can get this when only looking at one cell but not when I’m trying to look
a multiple ones.

With data arranged as below; try the below user defined formula

Col A Col B Col C Col D
bg small large larger
2 3 4 1

Try this UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

=HLOOKUP_CONCAT(A22,A11)

Function HLOOKUP_CONCAT(lookup_Range As Range, ret_Range As Range)
Dim lngCol As Long
For lngCol = 1 To lookup_Range.Columns.Count
If lookup_Range(lngCol) = 3 Then HLOOKUP_CONCAT = _
HLOOKUP_CONCAT & "," & ret_Range(lngCol)
Next
HLOOKUP_CONCAT = Mid(HLOOKUP_CONCAT, 2)
End Function


If this post helps click Yes
---------------
Jacob Skaria


"Bricky" wrote:

I'm trying to get a formula using concatenate that will identify numbers in a
row 3 (4&5) and returning the name of the column above into one cell.

Big small large larger
1 4 5 2

So in one cell I should get [small, large]
I can get this when only looking at one cell but not when I’m trying to look
a multiple ones.

Thanks Jacob, unfortunately this is picking up all the data in row A11? Not
only values greater than 3.
Other than that works fine.

cheers Bricky

"Jacob Skaria" wrote:

With data arranged as below; try the below user defined formula

Col A Col B Col C Col D
bg small large larger
2 3 4 1

Try this UDF (User Defined function). From workbook launch VBE using
Alt+F11. From menu Insert a Module and paste the below function.Close and get
back to workbook and try the below formula.

=HLOOKUP_CONCAT(A22,A11)

Function HLOOKUP_CONCAT(lookup_Range As Range, ret_Range As Range)
Dim lngCol As Long
For lngCol = 1 To lookup_Range.Columns.Count
If lookup_Range(lngCol) = 3 Then HLOOKUP_CONCAT = _
HLOOKUP_CONCAT & "," & ret_Range(lngCol)
Next
HLOOKUP_CONCAT = Mid(HLOOKUP_CONCAT, 2)
End Function


If this post helps click Yes
---------------
Jacob Skaria


"Bricky" wrote:

I'm trying to get a formula using concatenate that will identify numbers in a
row 3 (4&5) and returning the name of the column above into one cell.

Big small large larger
1 4 5 2

So in one cell I should get [small, large]
I can get this when only looking at one cell but not when I’m trying to look
a multiple ones.