0

I am having difficulties creating this formula in Google Sheets. I have columns D,E,F with the headers:
Should Actual Diff.
Under D and E a drop down with Top Expert, Professional, Working Knowledge, Awareness of

I want the following conditions:

If "Top Expert" and "Top Expert" then "No Need"

If "Top Expert" and "Professional" then "Slight Need"

If "Top Expert" and "Working Knowledge" then "Need"

If "Top Expert" and "Awareness of" then "Strong Need"

If "Professional" and "Top Expert" then "No Need"

If "Professional" and "Professional then "No Need"

If "Professional" and "Working Knowledge" then "Slight Need"

If "Professional" and "Awareness of" then "Need"

If "Working Knowledge" and "Top Expert" then "No Need"

If "Working Knowledge" and "Professional then "No Need"

If "Working Knowledge" and "Working Knowledge" then "No Need"

If "Working Knowledge" and "Awareness of" then "Slight Need"

If "Awareness of" and "Top Expert" then "No Need"

If "Awareness of" and "Professional then "No Need"

If "Awareness of" and "Working Knowledge" then "No Need"

If "Awareness of" and "Awareness of" then "No Need"

I was able to create ONE formula for just one but then it stopped working

https://docs.google.com/spreadsheets/d/1Gtykc0ipLIjIB1YziwmGUAwQC7XqfeZ71-6Sfgw91f8/edit?usp=sharing

8
  • Hi Justin, welcome on WE ! Good pratice is to provide a dummy sheet with some data in it and make sure that we're able to edit. – Jacob Jan Tuinstra Jan 12 at 8:13
  • this reference should get you started. – 24601 Jan 12 at 9:29
  • @User24601 I used that link originally but it didn't help. – Justin Ehresman Jan 12 at 9:44
  • @Jacob Jan Tuinstra What do you mean? A Screenshot? ATM there is no content/formula in the cells. – Justin Ehresman Jan 12 at 9:45
  • 1
    "And ignoring 'NA'" does not make sense to me. Something needs to happen if 'NA' is chosen for Column D or Column E. And since you've listed only what should happen with two-part combos, it remains unclear what should happen if D or E is 'NA' (or if both are). Please clarify. – Erik Tyler Jan 12 at 10:57
0

Assuming that your drop-downs run from D2:D and E2:E and that you want to process for F2:F entirely, make sure F2:F is entirely empty and then add the following to F2:

=ArrayFormula(IF(D2:D&E2:E="",,IFERROR(IF(LEFT(D2:D)="A","No Need",IF(LEFT(D2:D)="W",IF(LEFT(E2:E)="A","Slight Need","No Need"),IF(LEFT(D2:D)="P",IF(LEFT(E2:E)="A","Need",IF(LEFT(E2:E)="W","Slight Need","No Need")),IF(LEFT(D2:D)="T",VLOOKUP(LEFT(E2:E),{"A","Strong Need";"W","Need";"P","Slight Need";"T","No Need"},2,FALSE),)))))))

I'm not going to explain the logic of this, since that is outlined in your original post.

There are a few parts of note, however:

LEFT(D2:D) etc.

This is one way to get the first character of a string, which shortens the formula as compared with typing out the whole key word or phrase each time. We can do this only because the first letter of each of your key words/phrases is unique.

(...)*(...)

This means both elements must return TRUE. It's the array version of AND(... , ...).

{"A","Strong Need";"W","Need";"P","Slight Need";"T","No Need"}

This is a virtual array, where comma denotes "to the right" and semicolon denotes "underneath."

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.