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 w...
excel
Formulas and Functions
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.

Resources