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:
plums
raisins
orange
nectar
apple
juice
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.