Which rows contain certain item?

BuffaloGuy

New Member
Joined
Dec 5, 2017
Messages
27
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
AppleFruit
2CarrotVegetable
3PeasVegetable
4LettuceVegetable
5WatermelonFruit
6CornVegetable
The Vegetables are:2, 3, 4, 6
The Fruits are:1, 5

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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):
Code:
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.
Code:
=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!!!).
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top