Multiple IF AND Statements with 6 possible outputs

user_7467431 0
Viewed 87

I'm trying to set up an excel formula containing multiple IF(AND statements. I tried this:

=IF(AND(C9="0",C8="65"),Tables!B6,
 IF(AND(C9="0",C8="70 to 75 depending on which bank"),Tables!C6,
 IF(AND(C9="1",C8="65"),Tables!D6,
 IF(AND(C9="1",C8="70 to 75 depending on which bank"), Tables!E6,
 IF(AND(C9="2 or More",C8="65"), Tables!F6,
 IF(AND(C9="2 or More",C8="70 to 75 depending on which bank"), Tables!G6, "NULL"))))))


and all I got was "NULL". Not sure how to fix this.

Here is a visualization of what I'm trying to do, with 6 possible outputs depending on the conditions met: Photo of what I am trying to do

excel-formula excel
2 Answers

You can use a lookup table, like this: 

0&65    A
0&75    B
1&65    C
1&75    D
2&65    E
2&75    F

If your lookup table is in A1:B6 then your formula would be:

=VLOOKUP(C9&"&"&C8,$A$1:$B$6,2,0)
user_28593470

You can use this formula. You had to nest another AND inside AND formula to apply condition from 70 to 75. No need for helper column. 

=IF(AND(C9=0,C8=65),Tables!B6,IF(AND(C9=0,AND(C8>=70,C8<=75)),Tables!C6,IF(AND(C9=1,C8=65),Tables!D6,IF(AND(C9=1,AND(C8>=70,C8<=75)),Tables!E6,IF(AND(C9>=2,C8=65),Tables!F6,IF(AND(C9>=2,AND(C8>=70,C8<=75)),"F","NULL"))))))
user_82596470
Related