Regexp for several search&replace

I have a problem to get regexp working. I need to find 3 diffrent text occurances in column A which has 20K rows and replace them based on text with different numeric values on column B.
Pseudo example:
A column text:

B values if A contains nectar & juice: 2.2
B values if A contains orange & apple: 2.4
B values if A contains plums & raisins: 2.6

Tried many different approaches and none worked all ready.

EDIT: Sorry that I was in a hurry and had no time to be more precice:
1.) Column B only holds 1 value at a time (digit) 2,2 or 2,4 or 2,6 Not all A column rows contain 2 values like nectar & juice, they might have something like “Mumbo Jumbo nectar 1”
2) Column B is actually a pricing factor and it depends on found words from A column, so if Plums & raisins are found they get the highest value, nectar & juice or nectar OR juice get the lowest value.
3.) A column can hold other words too.
4.) All words in column A are separate words like in sentence “Mumbo Jumbo Juice”
5.) I will use , rather than period as a decimal separator but that is not the issue, just a typo from excel days.

I have tried something like example below but it is not working veru well, also wildcard and some piping for queries would be needed:

=IF( A2=“Juice”; “2,2”; IF( OR(A2=“raisins”;A2=“Plum”);“2,4”;“2,6”) )

EDIT2: I did not notice the last question about “combined score”. The B column acts as a base for row aka. product pricing which is derived from title in A (cheap–>Juice -->Raisins–>Plum–>expensive) column, price multiplier in B column and price in C column. B column multiplier is needed because not all products have same costs and they have not properly taken into account in C column price.

Ambiguous request

  • Is that column of text your column A, containing single words to match? Do you want to set the values in column B when column A matches one or the other, or when both match simultaneously?
  • Do you want column B to hold a copy of column A, only replacing words in matching rows with the respective digits?
  • “orange nectar and pear juice” becomes “orange 2 and pear 2”
  • “orange juice and apple juice” becomes “2 juice and 4 juice”
  • Do you want column B to hold the text “2.2”, “2.4” etc. if both given words are found in column A?
  • Are you using the period as decimal separator, and want only the numerals in column B?
  • Could a matching cell contain other words in addition to the ones you ask for?
  • Should the matches be separate words, or may they be random sections in a row of characters?
  • Is there some kind of “combined score” which follows from the column of text entries, or are those

@TTFLBR: I edited the question to correct spelling and formatting, but the corrections have been removed and the mistakes have been added back in. Because of this, I have downvoted the question. To me, it is unacceptable to ignore corrections that should be made. I will remove the downvote if you are willing to fix this problem. If you’re not sure what I mean, click on the “updated…” link to see the revision history.

First, do not use string results (“2,2”;“2,4”;“2,6”) if you want to calculate with numbers, use numeric data instead (2,2;2,4;2,6).

Second, the = equal operator compares for, well, egality, not a substring of a cell content.

So, replacing each = operator with NOT(ISERROR(SEARCH(…))), your formula probably should look like:

=IF( NOT(ISERROR(SEARCH("juice";A2))); 2.2; IF( OR( NOT(ISERROR(SEARCH("raisins";A2))); NOT(ISERROR(SEARCH("plum";A2)))); 2.4; 2.6))

which can be shortened to use less function calls as

=IF( ISERROR(SEARCH("juice";A2)); IF( NOT(AND( ISERROR(SEARCH("raisins";A2)); ISERROR(SEARCH("plum";A2)))); 2.4; 2.6); 2.2)

Note this uses . dot decimal separator, if your locale uses , comma decimal separator then adjust the numeric values accordingly.

Thank you erAck. I combined both of your answers and now I have a fine pricing formula.