If Function with Multiple Text Criteria in Same Cell

1
votes

I've got an google sheet that I copy paste into an excel sheet. One of the columns in the google sheet contains options that a client selected out of; Lift Pass, Rentals, Lessons, and Helmet The selected options (Lift Pass, Helmet, Rentals, Lessons) are in one cell, with the client info in separate cells.

What I'm trying to do is have an If function return 1 if certain products are selected. Currently I can get the function to work if there's only one product selected (I.e. "Lift Pass" only), but I can't figure out how to make it work with multiple products selected.

My working functions:

Lesson Only:

=IF(AND(NOT(OR('Paste Here'!B2="Lift Pass",'Paste Here'!B2="Rentals")),'Paste Here'!B2="Lessons"),1,"")

Rental Only:

=IF(AND('Paste Here'!B2="Rentals",NOT(OR('Paste Here'!B2="Lift Pass",'Paste Here'!B2="Lessons"))),1,"")

Lift Pass only:

=IF(AND('Paste Here'!B2="Lift Pass",NOT(OR('Paste Here'!B2="Rentals",'Paste Here'!B2="Lessons"))),1,"")

The products are listed in the B:B column. I've tried a SUMIF, COUNTIF and a few others only to receive #VALUE back. Each client should end up with a 1 under the column that specifies what products they've ordered.

This is the data I'm working with:

enter image description here

Help?

1
excelexcel-formulaexcel-2007
Please share/show your (sample)data - JvdV
I can't really say I understand what you are trying to do, but I think if I had your problem I would do something like this, or if I needed to know what the selection was exactly in words instead of having to look at 4 columns, then use different 'scores' for each item instead of using 1 everywhere which will allow me to use a lookup, something like this (you see the Ref 1 corresponds to lesson only, 2 to rental only, etc) - Jerry

1 Answers

0
votes

If your formula is on google sheets, use REGMATCH. If your formula is in Excel, then use SEARCH.

The example I set up was to enter "Rentals, Helmet, Lessons" into cell A1.

  • In google sheets then, in cell B1 I used the formula =if(REGEXMATCH(A1,"Helmet"),1,"").
  • In Excel then, in cell B1 I used the formula =IF(IFERROR(SEARCH("Helmet",A1),0)>0,1,"")

The SEARCH formula will return an error if the string is not found in the cell, so it must be handled similar to the example.

You can stack these up in If statements as much as needed. As an example, if you need to detect if three of the products are present, stack up the IF statements (in Excel) like this:

=IF(IFERROR(SEARCH("helmet",A1),0)>0,IF(IFERROR(SEARCH("rental",A1),0)>0,IF(IFERROR(SEARCH("lift pass",A1),0)>0,1,""),""),"")