# nested If statement doesn't work

#### fhzhkunming

##### New Member
I want to get a value in Cell D20 based on the value of B9, C14 or C20. If B9<> 0, I want the value in B9 to be shown in D20 and don't worry about the rest; otherwise, if B9=0, then check C14; if C14 <>0, value of C14 goes to D20; otherwise, check C20. C20 has a dropdown list, each item has a corresponding value that should go to D20. Here is my nested if statement:
=IF(B9<>"",B9,
IF(C14<>"", C14,
IF(OR(C20="10-146",C20="10-110",C20="Extremely weak"),50,
IF(OR(C20="147-620",C20="111-1,315",C20="Weak"),500,
IF(OR(C20="630-2,690",C20="1,316-15,500",C20="Moderate"),5000,
IF(OR(C20="2,700-11,500",C20="15,600-184,000",C20="Moderately strong"),50000,
IF(OR(C20="11,600-49,700",C20="185,000-2,190,000",C20="Strong"),500000,
IF(OR(C20="49,800-213,000",C20="2,200,000+",C20="Extremely strong"),2200000,
IF(C20="214,000-916,000",10000000,
IF(C20="917,000-3,930,000",40000000,
IF(C20="3,940,000+",50000000)))))))))))
I couldn't get a value from dropdown in C20 to D20 if both B9 and C14 are 0. But if I took out "IF(C14<>"", C14," this part, the nested if statement seems to work.
I would appreciate it very much if anyone could help me out.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### jtakw

##### Well-known Member
Hi,

I'm not seeing a problem with your formula, except your description is testing for 0 in B9 & C14, but your formula is testing for Blank ""
so maybe just change that part?
I also cleaned up the formula a bit:

Excel Formula:
``````=IF(B9<>0,B9,IF(C14<>0, C14,IF(OR(C20={"10-146","10-110","Extremely weak"}),50,IF(OR(C20={"147-620","111-1,315","Weak"}),500,IF(OR(C20={"630-2,690","1,316-15,500","Moderate"}),5000,
IF(OR(C20={"2,700-11,500","15,600-184,000","Moderately strong"}),50000,IF(OR(C20={"11,600-49,700","185,000-2,190,000","Strong"}),500000,IF(OR(C20={"49,800-213,000","2,200,000+","Extremely strong"}),2200000,IF(C20="214,000-916,000",10000000,IF(C20="917,000-3,930,000",40000000,IF(C20="3,940,000+",50000000)))))))))))``````

#### Fluff

##### MrExcel MVP, Moderator
Hi & welcome to MrExcel.
Are both B9 & C14 both blank or 0?
Also you could clean the formula up considerably if you create a lookup table for the C20 values.

#### fhzhkunming

##### New Member
Hi jtakw,

I have a change event via VBA code. Once I change or select an option from C20, the content of B9 and C14 will be cleared out and that's why I put "", empty, to test. But before I go to C20, I may have values in either B9 or C14. If I get a value from either cells, I don't need to go to C20.
As for XL2BB coding format, I am using a computer belonging to my organization and don't have admin privileges to make any change to the computer. I tried to install the Add-in, but it's not available on my computer. This is my first time to post. I will look around and see if I can find a way to do it. Sorry for the inconvenience.

Thanks again.

Frank

#### fhzhkunming

##### New Member

Hi Fluff,
Thanks for your response. Yes, if I need to go C20, both B9 & C14 are blank.
Thanks,

Frank

#### Marcílio_Lobão

##### Well-known Member
Good evening.

Yours is a classic case for using the VLOOKUP function.
A separate table always makes the job easier.

It is always easy to find values and references when maintenance is required.

You can still use the id column itself to generate the list for data validation.

There follows a modest contribution to this case.

it is only the beginning of the table to complete it with the other values.

D20 -->
Excel Formula:
``=IF(B9<>0, B9, IF(C14<>0, C14, VLOOKUP(C20,G3:H12,2,FALSE)))``

I hope it helps.

#### Attachments

• Lookup_table.PNG
7.8 KB · Views: 1

#### Hi Marcílio_Lobão,​

Thanks for your suggestion. C20 is a dependent dropdown list, and its contents depends on another dropdown list in B20. Can I still use VLOOKUP? I am not familiar with VLOOKUP.

Thanks

#### Marcílio_Lobão

##### Well-known Member
fhzhkunming, Good evening.

Without being able to see the assembly of your spreadsheet, it is very difficult to make efficient indications.

Maybe if you save your spreadsheet on some free site, just put the link here so we can see the logic in the spreadsheet.

Is there any place where the texts in the list that will appear in C20 are written together with the values?

The VLOOKUP function is very simple but it is powerful.

#### fhzhkunming

##### New Member
Hi Members,

Thank you very much for your suggestions and comments. The ideas from you guys give me inspiration to solve the problem.
There is nothing wrong with my IF statement. However, C14 contains a formula and depends the input of C12, which contains a dropdown list with 2 options. I need to chose one specific option, default option, to make the IF statement in D20 working. To achieve this, I added one line in my Worksheet_Change VBA code, which changes C12 to the default option once any changes are made to C20. Problem solved!
I will try to find a way to post sample spreadsheet next time.
Thank you all!

F

#### Fluff

##### MrExcel MVP, Moderator
Glad you sorted it & thanks for the feedback.

Replies
2
Views
410

1,129,386
Messages
5,636,014
Members
416,892
Latest member
Bensch

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back