yogi_Using Alternative QUERY And FILTER Functions With Criteria As 'In-line strings' and 'Cell references (from dropDowns)'

Google Spreadsheet   Post  #2576

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-12-2019

question by: Adner Colon:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/6U0oGfKCsQg;context-place=mydiscussions
Hi I need help with if and function in Google Sheets
Hi,

I hope this question finds you all well. I am trying to compare two columns to create a list. However, the two columns may have a different response. For example, in the D column, we may have either three answers; "Yes, I can," "Sometimes, I can work," or "No, I"m not available. The same for column K where we may have either three responses; "I'm nice with it," "I'm alright," or "I' need help." Column C is the name of the workers.

I am trying to use the "if" and "and" function to compare the two columns. The formulas, I have tried are as follow:

=if({d2="Yes, I can",d2="Sometimes, I can work",K2="I'm nice with it",K2="I"m alright",C2,"")

it works, but it gives me workers who not available to work at a certain but who is I'm nice with it.

=if(and({d2="Yes, I can",d2="Sometimes, I can work",K2="I'm nice with it",K2="I"m alright"),C2,"")

but I keep getting errors.

My goal is if D have either "Yes, I can" or "Sometimes I can work" AND if K="I'm nice with it" or "I'm alright" then enter the name in column C., Please advise.

Thank you,

Adner