Hi , I am getting error for the below search criteria.
Please guide. I Need to find the parital matching cells of A in B
Here cell A1 matches with B2 ,
A2 matches with B3,
A3 matches with B1
I Have used formulas mentioned in forum INDEX($page2.$A$1:$A$733;MATCH(A1&".*";$page2.$B1:$B$733;))
And MATCH(A1&".*", $b$1:$b$10000, 0) but unable to match with above criteria.
Also i would like to SUMIFS values of C those partial matching in B for A. Please share formula for the same.
Thanks,
Edit 2021-03-16: BigRAl - retagged from common to calc.
What about =MATCH(".*"&A1&".*";$B$1:$B$10000;0)?
Please make sure “Enable regular expressions in formulas” is set correctly, not “Enable wildcards in formulas” Tools - Options - LibreOffice Calc - Calculate
Updated. Let’s take a closer look at how formulas and spreadsheet settings interact.
First of all, let’s agree that your search criteria should be limited to wildcards on both sides, left and right. As you tried to use it, Calc interprets it as “a search string followed by some characters.” Or in other words, “the cell value must start with the specified text”
At the same time, your sample of data shows that the text in the middle of the cell should be searched for, “some characters, then the search text and then some characters”.
“some characters” will be denoted by wildcards or regular expressions.
Now let’s take a look at this setting.
The setting values set here will be applied to the entire current spreadsheet and will be saved in the document.
If the first item is selected, then you will simply use an asterisk * to denote “multiple characters”.
=MATCH("*"&A3&"*",$B$3:$B$10000,0)
=INDEX($B$3:$B$10000,MATCH("*"&A3&"*",$B$3:$B$10000,0))
=VLOOKUP("*"&A3&"*",$B$3:$B$10000,1,0)
If the second item is selected, then the regular expression must be a dot-asterisk .*.
=MATCH(".*"&A3&".*",$B$3:$B$10000,0)
=INDEX($B$3:$B$10000,MATCH(".*"&A3&".*",$B$3:$B$10000,0))
=VLOOKUP(".*"&A3&".*",$B$3:$B$10000,1,0)
Be careful! If the search string contains characters that are included in the List of Regular Expressions, then you will receive an erroneous result.
The choice of the third option “No wildcards or regular expressions in formulas” is required extremely rarely and for very specific tasks. So just don’t set this option.
@mariosv I meant that the three of us - @Macho1, @anon73440385, and me - used regexp .* in the formula. If “Enable wildcards in formulas” is specified in the Settings by mistake, then MATCH() will not work correctly.
Maybe I dd not read well first time, forgive.
@mariosv No, no, don’t apologize - your wording is even better: you remind users that regexps are not the only way to solve problems - wildcards can also be used. Would you like to post this as an answer?
I think should be enough if you refresh this answer.
Nice 
…
Thansk JohnSun. Please also guide what could be formula to be used incase if we use SUMIFS AND COUNTIFS function for the above same criteria.
Sorry @Macho1, your original question was about the problem of match. Your last request is about numeric functions. Look at your sample data in the question - to which piece of data do you want to apply SUMIFS or COUNTIFS?
Hi Johnsun , I would like to SUMIFS value in C cells for those partial match of A in B. Value of SUMIFS to be mentioned in E. attached image in question page.
=SUMIFS($C$1:$C$1000;$B$1:$B$1000;".*"&A1&".*") or just =SUMIF($B$1:$B$1000;".*"&A1&".*";$C$1:$C$1000)
Should i disable wildcards ? for using SUMIFS AND SUMIF
Please read these two points in the Help for the placeholder characters.
You need to Enable regular expressions in formulas if you use a placeholder string like ".*" (Regular expression), Enable wildcards in formulas if you use a placeholder string like "*" (wildcards). If disabled, then the formulas will not work at all as you ask in your question.
Thank you very much.
Hello,
probably you want something like =MATCH(".*" & A1 & ".*";$B$1:$B$3;0).
However, for this to work, you need to set option Tools -> Options -> LibreOffice Calc -> Calculate -> Option: [x] Enable regular expressions in formulas
Hope that helps.
