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

%3CLINGO-SUB%20id%3D%22lingo-sub-1734461%22%20slang%3D%22en-US%22%3ESearch%20for%20part%20of%20a%20word%20in%20a%20Column%20and%20show%20all%20results.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1734461%22%20slang%3D%22en-US%22%3E%3CP%3Ethis%20in%20my%20Data%3A%3C%2FP%3E%3CP%3E1.%20tom%3C%2FP%3E%3CP%3E2.%20tom%20h%20jones%3C%2FP%3E%3CP%3E3.%20tommy%3C%2FP%3E%3CP%3E4.%20tomie%3C%2FP%3E%3CP%3E5.%20thompson%3C%2FP%3E%3CP%3Ehow%20do%20I%20change%20this%20formula%20to%20seach%20for%20tom%20and%20bring%20up%20all%20of%20them%3F%3C%2FP%3E%3CP%3EIF(ROWS(J%247%3A%24K7)%26lt%3B%3D%24K%245%2CINDEX(%24A%246%3A%24A%2475%2CAGGREGATE(15%2C3%2C(%24B%246%3A%24B%2476%3D%24N%244)%2F(%24B%246%3A%24B%2476%3D%24N%244)*(ROW(%24B%246%3A%24B%2476)-ROW(%24B%245%3A%24C%245))%2CROWS(J%247%3A%24K7)))%2C%22%22)%3C%2FP%3E%3CP%3EThis%20formula%20only%20finds%20a%20exact%20match.%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1734461%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1735846%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20for%20part%20of%20a%20word%20in%20a%20Column%20and%20show%20all%20results.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735846%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816369%22%20target%3D%22_blank%22%3E%40Dean_Thatcher%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EInstead%20of%3C%2FP%3E%0A%3CP%3E%3CSPAN%3E(%24B%246%3A%24B%2476%3D%24N%244)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3Eyou%20may%20use%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EISNUMBER(SEARCH(%24N%244%2C%24B%246%3A%24B%2476))%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1743366%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20for%20part%20of%20a%20word%20in%20a%20Column%20and%20show%20all%20results.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1743366%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20You%20for%20this%20information.%3C%2FP%3E%3CP%3EThere%20are%202%20places%20to%20enter%20your%20change%20and%20it%20worked.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1752027%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20for%20part%20of%20a%20word%20in%20a%20Column%20and%20show%20all%20results.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1752027%22%20slang%3D%22en-US%22%3E%3CP%3E%40this%26nbsp%3Bis%20my%20file%20name%20that%20I%20used%20this%20in.%3C%2FP%3E%3CP%3EExcel_Finding_Multiple_Matches_Xelplus_%20AGGREGATE%20PCSB%2010-04-20%201100PM.xlsx%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

3 Replies

@Dean_Thatcher 

Instead of

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

you may use

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

Thank You for this information.

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

 

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

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