return a yes or no dependant on specific text in multiple cells... and combinations of that text

Copper Contributor

Hi all,


Pretty new hear first post...


I need to add a cell which will show when a signature is required for specific payment type and shipping method. 


Column B = payment type and could be one of many

Column C = Shipping Method and could be one of many

Column D = weather the combo of B and C require signature "yes" or "no"


ie: Combination of "Visa" (B2) and "UPS ground" (C2) = D2 "Yes"

 Combination of Mastercard (b3) and "Fed ex" (C3) = D3 "No"


i have found a way to do this with three separate cells and formulas but would like to have the entire formula in one cell to return a yes or no in column D ...any help would be greatly appreciated. 

1 Reply

Hi @walshcr ,


I found two possible solutions for you, as you can check in the attached file.


1. IFS

The first solution solves the problem with only one formula, but makes it a little complex if you are dealing with a large number of possible combinations. In the future, if you have to change these combinations, you probably will find some trouble.


It basically put all of your conditions in a IFS statement returning as "Yes", and returns "No" as default.




2. Validation table

This solution uses a validation table to you store all of the possible combinations that will result in "yes" to signature. The formula basically counts the rows of this validation table that contains the payment type and shipping type (ideally 0 or 1), and return "Yes" if the count is higher than zero.


The advantage of this approach is the fact that you can easily add/remove new combinations in your validation table. 







As I understand that you may need specifically a "one cell solution", the IFS approach may be better to your case.


Let me know if it helps you.