0

I am trying to use IF AND together for multiple conditions but its shows #VALUE error.

Following is the formula:
=IF(AND(X160<90,G160="Auto"),W167,100%),IF(AND(X160<90,G160="Commercial"),W168,100%),IF(AND(X160<90,G160="Real Estate"),W169,100%)

Please help me with this error, thanks

Improve this question
8
  • I see 3 separate correct formulas divided by a comma, which totally forms one incorrect formula. And none = signs at all. Edit your question, separate formulas one formula by line with = prefix.
    – Akina
    Nov 7, 2019 at 9:08
  • i have edited question formula with = sign. first condition is that X160 cell should be less than 90 and if G160 cell will have various text like auto, commercial , real estate , then it should choose their corresponding value from cell W167, W168, W169. I want it to be one formula.
    – Sammy
    Nov 7, 2019 at 9:35
  • In such form the formula is absolutely incorrect. I'd recommend you to post the task - i.e. show some example data and desured result.
    – Akina
    Nov 7, 2019 at 9:37
  • i actually want that if G160 has "auto", "commercial" or "real estate" text and cell X160 is less than 90 days, than formula which is in AB160 cell should pick up value from cell W167 (for auto), W168 (for commercial) and W169 (for real estate). Otherwise if X160>90,then its should show "100%" for all text "auto", "commercial" or "real estate" text.
    – Sammy
    Nov 7, 2019 at 9:55
  • 1
    You try to use a chain =IF( .. , .. , .. ) , IF( .. , .. , .. ) , .. whereas you need in nested formula =IF( .. , .. , IF( .. , .. , .. ) ).
    – Akina
    Nov 7, 2019 at 10:15

1 Answer 1

Reset to default
1

G160 will always have "auto", "commercial" or "real estate" text. X160 will never be equal, either bigger or less than 90 days.

=IF(X160>90, "100%", IF(G160="Auto", W167, IF(G160="Commercial", W168, W169)))
Improve this answer
2
  • many thanks. it worked :) someone told me to use AND and i got stuck :D
    – Sammy
    Nov 7, 2019 at 10:34
  • @Sammy use AND inside an IF when you need multiple separate conditions to be true to satisfy the IF.
    – Alex M
    Nov 7, 2019 at 17:24

You must log in to answer this question.

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