CODEWITHSUNDEEP
excelgoogle-sheetsspreadsheet
Michael Tyson
Michael Tyson

Reputation: 3

if specific text is in cell return a certain value. multiple "specific texts" and multiple values

1

I have this spread sheet that currently i manually fill out much to my frustration, as I can't work out how to automate it.

Columns a b and c are from an export from my webstore. I use this sheet to export all the orders for the week and then figure out how much of what coffee I need to order. The export is clunky and product info is grouped together.

What I want to do is... If any cells in B contain "Blend subscription" then place value into column F If any cells in B contain "Filter subscription" then place value into column D If any cells in B contain "Espresso subscription" then place value into column E

The values are 250, 450, 900 and relate to a specific text in column C If cells in column C contain "small" then value is 250 If cells in column C contain "medium" then value is 450 If cells in column C contain "large" then value is 900

The values relate to grams of coffee. They are normally input by me manually

I hope this makes sense because It's confusing me! I've tried versions of Vlookup and countif but nothing quite works.

Upvotes: 0

Views: 477

Answers (1)

rockinfreakshow
rockinfreakshow

Reputation: 30281

Within Sheets here's a sample setup and a working formula you can test out.

Cell D2 formula:

=INDEX(MAP(B2:B8,C2:C8,LAMBDA(bx,cx,IF(REGEXMATCH(bx,"(?i)"&D1:G1&" subscription"),SWITCH(REGEXEXTRACT(cx,"Grind: (.*?),"),"Small",250,"Medium",450,"Large",900),))))

-

enter image description here

FINAL UPDATED FORMULA:

=INDEX(MAP(B2:B9,C2:C9,LAMBDA(bx,cx,IF(bx="",,IF(REGEXMATCH(bx,"(?i)"&D1:G1&" subscription"),SWITCH(REGEXEXTRACT(cx,"Small|Medium|Large"),"Small",250,"Medium",450,"Large",900),)))))

Upvotes: 1

Related Questions