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?

1	Apple	Fruit
2	Carrot	Vegetable
3	Peas	Vegetable
4	Lettuce	Vegetable
5	Watermelon	Fruit
6	Corn	Vegetable
	The Vegetables are:	2, 3, 4, 6
	The Fruits are:	1, 5

<tbody>
</tbody>

 

[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!!!).