Search for part of a word in a Column and show all results.

[Dean_Thatcher]

this in my Data:

1. tom

2. tom h jones

3. tommy

4. tomie

5. thompson

how do I change this formula to seach for tom and bring up all of them?

IF(ROWS(J$7:$K7)<=$K$5,INDEX($A$6:$A$75,AGGREGATE(15,3,($B$6:$B$76=$N$4)/($B$6:$B$76=$N$4)*(ROW($B$6:$B$76)-ROW($B$5:$C$5)),ROWS(J$7:$K7))),"")

This formula only finds a exact match.

thanks

[#2]

@Dean_Thatcher 

Instead of

($B$6:$B$76=$N$4)

you may use

ISNUMBER(SEARCH($N$4,$B$6:$B$76))

[#3]

Thank You for this information.

There are 2 places to enter your change and it worked.

[#4]

@this is my file name that I used this in.

Excel_Finding_Multiple_Matches_Xelplus_ AGGREGATE PCSB 10-04-20 1100PM.xlsx