3 IF Statements

[Ian_A85]

Hi all,

Really simple one today, I need to know how to put 3 If statements in one formula

I have to use:
=IF(U:U="Supplier1",100,0)
=IF(U:U="Supplier2",125,0)
=IF(U:U="Supplier3",150,0)

All 3 work seperately, but i need them in one cell, please help!

 

[dodger7]

Im not sure about your U:U - how can a whole column equal a string? If you mean each cell in the column, try this:

=IF($U1="Supplier1",100,IF($U1="Supplier2",125,IF($U1="Supplier3",150,0)))

and copy it down

 

[Ruddles]

=CHOOSE(MATCH(U:U,{"Supplier1","Supplier2","Supplier3"},FALSE),100,125,150)

 

[Ian_A85]

Thats great, the CHOOSE option is brilliant, one final thing, how easy it to add a IF none are chosen, it returns 0?

 

[Ruddles]

Easy to do but the formula starts to get messy!

=IF(ISNA(MATCH(U:U,{"Supplier1","Supplier2","Supplier3"},FALSE)),0,CHOOSE(MATCH(U:U,{"Supplier1","Supplier2","Supplier3"},FALSE),100,125,150))

If you're going to start adding lots more suppliers I would recommend placing the names and values in a separate lookup table and using VLOOKUP: that would make the formula simpler and it means you don't have to change the list wherever it occurs in a formula - just once in the lookup table.