# Nested IF statements or CHOOSE statements needed for multiple conditions formula

1. ## Nested IF statements or CHOOSE statements needed for multiple conditions formula

I have the text in two column - example column R has "High" and column s has "annual".

I have a set of conditions that I need the formula to perform:

if Column R has "high" and column S has "annual", the formula should display the words "1" in column U.

if Column R has "medium" and column S has "annual", the formula should display the words "1" in column U.

if Column R has "low" and column S has "monthly", the formula should display the words "1" in column U.

if Column R has "high" and column S has "quarterly", the formula should display the words "3" in column U.

if Column R has "medium" and column S has "quarterly", the formula should display the words "2" in column U.

if Column R has "low" and column S has "quarterly", the formula should display the words "1" in column U.

Thanks!!

2. ## Re: Quick formula help- should be easy for the techies!!

BTW, I am on Excel 2007, if that matters. Thanks.

3. ## Re: Quick formula help- should be easy for the techies!!

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

4. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

I used some shorthand for the formula (H for high, M for Medium, etc.) but you get the picture...

I've left blanks ("") for the conditions you did not define (like R1=Highand S1=Monthly).

5. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

Thank you for revising your title salmansaif - good titles aid searches.

Welcome to the Board.

6. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

U can use the long way by IF functionusing formula

=IF(OR(AND(R1="high",S1="annual"),AND(R1="medium",S1="annual"),AND(R1="low",S1="quarterly"),AND(R1="low",S1="monthly")),1,IF(AND(R1="high",S1="quarterly"),3,2))

Or create a small table near and use SUMPRODUCT

=SUMPRODUCT((R1=N2:N7)*(S1=O2:O7)*(P2:P7))

In short see attached file

7. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

Thanks for all your help but for some reason, the formula as I put it is not erroring out, but still giving the wrong answer.

I am attaching the spreadsheet - the formula needs to be in column "U" based on the blue "key" columns.

So for example, if R7 is medium and S7 is monthly, based on the key, the formula should generate "3" in column U.

8. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

salmansaif,

If you take a look at ContaminatedWitExcel's attachment you will see that the results you have match his IF statement (Medium + Monthly = 2).

My origininal IF statement will return a blank since that is not a condition you specified. Since it looks like you will have "other" conditions I would not use either IF statement. I recommend you follow ContaminatedWitExcel's advice and use a lookup table, however I would use INDEX and MATCH instead.

See my modifications of his file (I did not use yours except to verify the IF statement was correct).

9. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

I think u should provide us with all conditions...

In any case I've done some changes by adding new sheet, which u can hide in the future.

See book pls..

10. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

well they wouldnt medium/monthly is not in your original list of requirements
but the table with
=SUMPRODUCT((R1=N2:N7)*(S1=O2:O7)*(P2:P7))
idea is worth persuing then you can have them all

11. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

Chance2 created better table than me.

I used this table in source sheet. It'll easy for editing
Formula is:

=IF(ISERROR(OFFSET('Source sheet'!\$A\$1,MATCH(KY!R7,'Source sheet'!\$A\$2:\$A\$4,0),MATCH(KY!S7,'Source sheet'!\$B\$1:\$D\$1,0))),"",OFFSET('Source sheet'!\$A\$1,MATCH(KY!R7,'Source sheet'!\$A\$2:\$A\$4,0),MATCH(KY!S7,'Source sheet'!\$B\$1:\$D\$1,0)))

12. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

thank u all so much!

13. ## Re: Nested IF statements or CHOOSE statements needed for multiple conditions formula

If you want to get away from nested ifs, try using a lookup table with all your conditions and in column T, use vlookup to generate the value. I've included an attachement to show you what I did. What's nice about using a lookup table is you can easily add or modify it without modify the formula in column T.

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1