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 Im 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(A2:D2,A1:D1)

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 Im trying to look
a multiple ones.

Thanks Jacob, unfortunately this is picking up all the data in row A1:D1? 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(A2:D2,A1:D1)

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 Im trying to look
a multiple ones.