On Thu, 13 Sep 2012 16:29:24 +0000, grantt wrote:


Hi all

Please see the attached.

I am trying to use the value in column G to find which columns on that
row contain this value. I then want to list the column headings for each
column which contain that value. So far I have something working that
lists the first column heading, but I need to list all column headings,
ideally in a single cell separated by commas.

Note that some cells contain the lookup letter, in addition to another
letter. These must not be included in the above formula. I also need a
way to list the column headings of these guys (which contain 2 letters,
one of which is in column G) in a separate cell.

Please help.

Thanks.


+-------------------------------------------------------------------+
|Filename: book2.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=580|
+-------------------------------------------------------------------+

This can be accomplished with a long, nested formula, that examines each cell in turn; or more simply with an easy to write User Defined Function.

To enter this User Defined Function (UDF), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

Note that this workbook will need to be saved as an .xlsm or .xlsb workbook, as an .xlsx workbook cannot contain macros.

To use this User Defined Function (UDF), enter a formula like

=MatchG($A2:$F2,$G2) to obtain the exact match or
=MatchG($A2:$F2,$G2,TRUE) to obtain a match if the value in G is found anywhere in the other cells

============================================
Option Explicit
Function MatchG(RangeToSearch As Range, sFind As String, Optional Inclusive As Boolean = False) As String
Dim c As Range
Dim sTemp As String
If Inclusive = False Then
For Each c In RangeToSearch
If UCase(c.Text) = UCase(sFind) Then
sTemp = sTemp & ", " & Cells(1, c.Column).Text
End If
Next c
Else
For Each c In RangeToSearch
If InStr(1, c.Text, sFind, vbTextCompare) 0 Then
sTemp = sTemp & ", " & Cells(1, c.Column).Text
End If
Next c
End If

MatchG = Mid(sTemp, 3)

End Function
===============================================