Forum Discussion

jwobrien's avatar
jwobrien
Copper Contributor
Oct 05, 2020

Partial text match and display in a separate worksheet

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

 

excel
Formulas and Functions

5 Replies

  • Rajesh_Sinha's avatar
    Rajesh_Sinha
    Iron Contributor
    Oct 05, 2020

    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 :

     

    • 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.
    • jwobrien's avatar
      jwobrien
      Copper Contributor
      Oct 05, 2020

      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 

      • JMB17's avatar
        JMB17
        Bronze Contributor
        Oct 05, 2020
        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))

Resources