Partial text match and display in a separate worksheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1743989%22%20slang%3D%22en-US%22%3EPartial%20text%20match%20and%20display%20in%20a%20separate%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1743989%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHi%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI'm%20trying%20to%20find%20and%20understand%20the%20most%20appropriate%20formulas%20for%20finding%20and%20displaying%20a%20series%20of%20partial%20text%20strings%20and%20subsequently%2C%20the%20dollar%20values%20associated%20with%20them%20to%20separate%20worksheets%20from%20two%20data%20sources.%20The%20first%20screen%20shot%20displays%20example%20data%20from%20the%20first%20data%20source.%20The%20second%20screen%20shot%20displays%20example%20data%20from%20the%20second%20data%20source.%20I%20need%20to%20match%20the%20project%20names%20contained%20within%20these%20two%20data%20sources%20(and%20their%20values)%20to%20another%20worksheet%20to%20complete%20a%20comparison%20of%20the%20cost%20values.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EHope%20this%20explanation%20is%20clear%20-%20please%20messge%20me%20if%20you%20need%20more%20information.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThanks%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1743989%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1744024%22%20slang%3D%22en-US%22%3ERe%3A%20Partial%20text%20match%20and%20display%20in%20a%20separate%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1744024%22%20slang%3D%22en-US%22%3EIf%20you%20can%20download%20and%20install%20add-ins%2C%20you%20might%20try%20the%20fuzzy%20match%20add-in.%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D15011%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D15011%3C%2FA%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1744025%22%20slang%3D%22en-US%22%3ERe%3A%20Partial%20text%20match%20and%20display%20in%20a%20separate%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1744025%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%2C%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20that.%20I%20did%20quite%20a%20bit%20of%20reading%20prior%20to%20posting%20around%20the%20'limitations'%20of%20vlookup%20text%20strings%20and%20the%20need%20to%20potentially%20use%20the%20fuzzy%20match.%20I%20would've%20thought%20that%20there%20was%20another%20way%20to%20simply%20find%20and%20display%20the%20text%20from%20one%20worksheet%20to%20another.%20For%20example%2C%20the%20projects%20all%20have%20the%20BBR%20prefix%20-%20I%20thought%20it%20was%20possible%20to%20find%20and%20display%20any%20text%20containing%20'BBR'%20to%20display%20in%20the%20comparison%20worksheet%20that%20I've%20created%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1744027%22%20slang%3D%22en-US%22%3ERe%3A%20Partial%20text%20match%20and%20display%20in%20a%20separate%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1744027%22%20slang%3D%22en-US%22%3ERe-reviewing%20your%20first%20post%2C%20you%20mentioned%20a%20third%20worksheet%20that%20you%20are%20matching%20to%20these%20two%20(and%20not%20matching%20these%20two%20to%20each%20other)%3F%3CBR%20%2F%3E%3CBR%20%2F%3EIf%20the%20third%20worksheet%20has%20a%20keyword%20phrase%20that%20will%20definitely%20be%20contained%20within%20the%20other%20two%20worksheets%2C%20then%20you%20could%20use%20Index%2FMatch%20to%20perform%20the%20lookup%20using%20wildcards.%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIndex(RangeToReturn%2C%20Match(%22*%22%26amp%3Bkeyword%26amp%3B%22*%22%2C%20RangeWithDescriptions%2C%200))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1744239%22%20slang%3D%22en-US%22%3ERe%3A%20Partial%20text%20match%20and%20display%20in%20a%20separate%20worksheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1744239%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F819720%22%20target%3D%22_blank%22%3E%40jwobrien%3C%2FA%3E%26nbsp%3B%2C%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWrite%20me%20some%20sample%20text%20string%20to%20math%20Like%20Unit%20Rate%20sheet%20has%20column%201%20with%20values%20Engineering%20ID%2C%2C%2C%20and%20others%20and%20sheet%20Estimate%20has%20%3CSPAN%3EB%3C%2FSPAN%3E%3CSPAN%3EBR%3C%2FSPAN%3E%26nbsp%3B%2C%2C%2C%20what%20you%20want%20from%20both%20sheet%20as%20partial%20match%20%2C%2C%20give%20some%20example%20then%20I'll%20show%20U%20FORMULA%20to%20pull%20records!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EMeanwhile%20you%20may%20try%20this%20%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1601876975095.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F224102i1DDAB4D8D096C62E%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22Rajesh-S_0-1601876975095.png%22%20alt%3D%22Rajesh-S_0-1601876975095.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EAn%20array%20(CSE)%20formula%20in%20cell%20A56%3A%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%7B%3DIFERROR(INDEX(%24A%2448%3A%24C%2451%2CSMALL(IF(COUNTIF(%24A%2448%3A%24A%2451%2C%22*%22%26amp%3B%24A%2453%26amp%3B%22*%22)*COUNTIF(%24B%2448%3A%24B%2451%2C%24B%2453)%2CROW(%24A%2432%3A%24C%2437)-MIN(ROW(%24A%2432%3A%24C%2437))%2B1)%2CROW(A1))%2CCOLUMN(A1))%2C%22%22)%7D%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.%3CSPAN%3EB.%26nbsp%3B%26nbsp%3B%3C%2FSPAN%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3E%3CFONT%20color%3D%22%235f6368%22%3E%3CFONT%20face%3D%22arial%2C%20sans-serif%22%3E%3CSPAN%3ERed%20Cells%20are%20the%20%3C%2FSPAN%3E%3C%2FFONT%3E%3CSPAN%3Ecriteria%3C%2FSPAN%3E%3CFONT%20face%3D%22arial%2C%20sans-serif%22%3E%3CSPAN%3E.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3E%3CFONT%20color%3D%22%235f6368%22%3E%3CFONT%20face%3D%22arial%2C%20sans-serif%22%3E%3CSPAN%3EDon't%20enclose%20formula%20with%20%7B%20%7D.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FLI%3E%3CLI%3EFinish%20the%20formula%20with%20%3CSTRONG%3ECtrl%2BShift%2BEnter%2C%26nbsp%3B%3C%2FSTRONG%3E%3CEM%3E(%20Excel%20will%20enclose%20formula%20with%20%7B%20%7D%20).%3C%2FEM%3E%3C%2FLI%3E%3CLI%3EIn%20COUNTIF(%24A%2448%3A%24A%2451%2C%22*%22%26amp%3B%24A%2453%26amp%3B%22*%22)%20%2C%2C%2C%3CEM%3E%26nbsp%3B%22*%22%26amp%3B%24A%2453%26amp%3B%22*%22%3C%2FEM%3E%20works%20for%20partial%20match.%3C%2FLI%3E%3CLI%3EAdjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FLI%3E%3C%2FUL%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I'm trying to find and understand the most appropriate formulas for finding and displaying a series of partial text strings and subsequently, the dollar values associated with them to separate worksheets from two data sources. The first screen shot displays example data from the first data source. The second screen shot displays example data from the second data source. I need to match the project names contained within these two data sources (and their values) to another worksheet to complete a comparison of the cost values.

Hope this explanation is clear - please messge me if you need more information.

Thanks

 

5 Replies
If you can download and install add-ins, you might try the fuzzy match add-in.

https://www.microsoft.com/en-us/download/details.aspx?id=15011

Hi ,@JMB17 

Thanks for that. I did quite a bit of reading prior to posting around the 'limitations' of vlookup text strings and the need to potentially use the fuzzy match. I would've thought that there was another way to simply find and display the text from one worksheet to another. For example, the projects all have the BBR prefix - I thought it was possible to find and display any text containing 'BBR' to display in the comparison worksheet that I've created?

 

Cheers 

Re-reviewing your first post, you mentioned a third worksheet that you are matching to these two (and not matching these two to each other)?

If the third worksheet has a keyword phrase that will definitely be contained within the other two worksheets, then you could use Index/Match to perform the lookup using wildcards.

=Index(RangeToReturn, Match("*"&keyword&"*", RangeWithDescriptions, 0))

@jwobrien ,,

 

Write me some sample text string to math Like Unit Rate sheet has column 1 with values Engineering ID,,, and others and sheet Estimate has BBR ,,, what you want from both sheet as partial match ,, give some example then I'll show U FORMULA to pull records!!

 

Meanwhile you may try this :

Rajesh-S_0-1601876975095.png

 

  • An array (CSE) formula in cell A56:

 

 

 

{=IFERROR(INDEX($A$48:$C$51,SMALL(IF(COUNTIF($A$48:$A$51,"*"&$A$53&"*")*COUNTIF($B$48:$B$51,$B$53),ROW($A$32:$C$37)-MIN(ROW($A$32:$C$37))+1),ROW(A1)),COLUMN(A1)),"")}

 

 

 

N.B.  

  • Red Cells are the criteria.
  • Don't enclose formula with { }.
  • Finish the formula with Ctrl+Shift+Enter, ( Excel will enclose formula with { } ).
  • In COUNTIF($A$48:$A$51,"*"&$A$53&"*") ,,, "*"&$A$53&"*" works for partial match.
  • Adjust cell references in the formula as needed.

@JMB17

Sorry - My explanation wasn't clear. I have 3 data sources (1 from Primavera P6 and 2 excel files) - all contain the same projects with differing estimated project $ values as well as the detailed data of how the estimates are built up. My task is to compare the three estimated $ values and understand where they vary.  The project nomenclature varies across the three data sources but the product name prefix is consistent i.e. BBR as is the kilometre start and finish markers - so those are the unique identifiers. The first task is to display the project names from the three data sources on a separate worksheet, firstly to confirm that the numbers of projects match (scope can be removed/changed at short notice and it isn't communicated well to the owners of the three data sources). 

Hope that make more sense.