CODEWITHSUNDEEP
excelexcel-formula
Tristan
Tristan

Reputation: 23

Multiple IF AND Statements with 6 possible outputs

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

Upvotes: 2

Views: 105

Answers (2)

Kresimir L.
Kresimir L.

Reputation: 2441

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"))))))

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

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)

Upvotes: 3

Related Questions