1

I need help with returning results for several keywords in multiple columns in an Excel sheet, and I'm having trouble finding a similar scenario using search engines, so apologies if this has already been asked.

I need my Column F - to read 'Ready' or 'Not Ready' (or true or false) depending on the info in both in Column D (one of two different keywords) AND Column E (match one from just two keywords - Yes and No).

The criteria are:

  • 'Matched' and 'Yes' = Ready
  • 'Complete' and 'Yes' = Ready
  • 'Complete' and 'No' = Not Ready
  • And all other statuses in Column D, ('Awaiting Check', 'In Progress' and 'Not Registered') should also equal "Not Ready" in F

Pic for reference:
Pic for reference here

I've found a way for it to discount everything that isn't 'Matched' as 'Not Ready':

=IF(AND(D9="Matched",E9="Yes"),"Ready","Not Ready")

But I can't figure out how to get it to read 'Complete' AND 'Yes' as 'Ready' outside of this.

Any guidance on this is appreciated.

Improve this question
6
  • 1
    Just add another ÌF-clause in the "ELSE"-part of your formula.
    – Dominique
    May 24 at 13:30
  • 1
    ...or try =IF(OR(E9<>"Yes";AND(D9<>"Matched";D9<>"Complete"));"Not ";"") & "Ready"
    – JohnSUN
    May 24 at 13:40
  • 1
    ...alternatively (slightly shorter, but with the same result) - =IF(AND(E9="Yes";OR(D9="Matched";D9="Complete"));"";"Not ") & "Ready"
    – JohnSUN
    May 24 at 13:47
  • Thanks, @Dominque, but I'm not sure where to add the IF clause as am a beginner!
    – Yasmin
    May 24 at 14:03
  • @JohnSUN Hmmm, thank you but neither of those works, unfortunately, and I receive an error message when inputting.
    – Yasmin
    May 24 at 14:06

1 Answer 1

Reset to default
2

I think the simplest way to do it would be to to add an OR to the first logic of your AND.

Like this:

=IF(AND(OR(D9="Matched",D9="Complete"),E9="Yes"),"Ready","Not Ready")

The other methods mentioned in the comments can work as well... but I think they're less clear.

  • @Dominique advised to add a nested IF into the value_if_false. Like this: =IF(AND(D9="Matched",E9="Yes"),"Ready",IF(AND(D9="Complete",E9="Yes"),"Ready","Not Ready"))
  • @JohnSUN advised to use a workaround by always having Ready and adding Not if not matching both. Like this: =IF(OR(E9<>"Yes",AND(D9<>"Matched",D9<>"Complete")),"Not ","")&"Ready" or =IF(AND(E9="Yes",OR(D9="Matched",D9="Complete")),"","Not ") & "Ready"
Improve this answer
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .