Which rows contain certain item?

[BuffaloGuy]

Sorry for the horrible description, but if I knew how to explain it better I could probably look it up.

But is there a function that will output to a single cell all the row numbers that reference a single description? In my example below, I'm trying to write a function for cells C7 & C8 to output the row number for its categories. If I can do that, I would like the answer to be "None" if the cell would be empty. Any Ideas?

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]1
[/TD]
[TD]Apple[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Carrot[/TD]
[TD]Vegetable[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Peas[/TD]
[TD]Vegetable[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Lettuce[/TD]
[TD]Vegetable[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Watermelon[/TD]
[TD]Fruit[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Corn[/TD]
[TD]Vegetable[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]The Vegetables are:[/TD]
[TD]2, 3, 4, 6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]The Fruits are:[/TD]
[TD]1, 5[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

Welcome to the Board!

If you are open to a VBA solution (create a function in VBA), I think I could come up with something.

 

[Aladin Akyurek]

If TEXTJOIN is available on your Excel...

In B7 control+shift+enter, not just enter:

=TEXTJOIN(", ",TRUE,IF($B$1:$B$6="vegetable",ROW($A$1:$A$6)-ROW($A$1)+1,""))

In B8 control+shift+enter, not just enter:

=TEXTJOIN(", ",TRUE,IF($B$1:$B$6="fruit",ROW($A$1:$A$6)-ROW($A$1)+1,""))

 

[Joe4]

I was playing around with this (just for kicks), and here is the VBA solution that I came up (in case your version of Excel does not have TEXTJOIN):

Function MyFind(rngSearch, strMatch As String) As String
'   rngSearch = range to search for matches (i.e. B1:B6)
'   strMatch = value to search for (i.e. "Fruit")

    Dim cell As Range
    Dim myString As String
    
    For Each cell In rngSearch
        If cell = strMatch Then
            myString = myString & cell.Row & ", "
        End If
    Next cell
    
    If Len(myString) > 0 Then
        MyFind = Left(myString, Len(myString) - 2)
    Else
        MyFind = "None"
    End If

End Function

So, you would then use this like any other Excel function, i.e.

=MyFind(B1:B6,"Vegetable")

By the way, if you are a Buffalo sports fan, I feel your pain!
Someday, our teams will be good again (I just hope in my lifetime!!!).