NESTED IFs with multiple Index and Match functions

[olga6542]

I am guessing I need multiple If functions with multiple Index and Match functions. Here is my current formula: =IFNA(INDEX(E5:E6,MATCH(B4,D5:D6,0)),0). My formula needs to meet the following criteria
1. If Eligible for Option 2, select numeric values from Option 2 and put it in cell C4 (based on the drop-down from cell B4). I linked the checkbox to cell E2, so I know E2=True is part of the formula
2. If Eligible for Option 2 is not selected, select numeric values from Option 1 and put it in cell C4 (based on the drop-down from cell B4)
3. IFNA (if not applicable or no value, return 0 in cell C4).

I would greatly appreciate your help.

[protonLeah]

name: Option1, refers to: =Sheet1!$D$5:$E$6
name: Option2, refers to: =Sheet1!$G$5:$H$6

C4:

Please Login or Register  to view this content.

[protonLeah]

hello.........

[kvsrinivasamurthy]

In C4

=IFERROR(IFERROR(INDEX($H$5:$H$6,MATCH(B4,$G$5:$G$6,0)),INDEX($E$5:$E$6,MATCH(B4,$D$5:$D$6,0))),"")