Multiple IF arguments in the one cell

[rooboyz]

Hi guys, I was wondering if anyone could help me out with these two? I have two different dilemmas, in two different spreadsheets.

In both spreadsheets I have tried IF statements (including AND formulas), and VLookup. But I’m continuously getting NAME errors, and I seem to have too many arguments for a normal IF statement.

In the first example, from B2 – B6 I have empty cells which I type in either of - Y, S or N.
In cells C2 – C6 I need to show the value of each individual cell based on whichever of Y, S or N is entered into column B.
The values I need to apply for each letter are - 20 for Y, 8 for S, 0 for N.
For example – If I had Y in B2, then 20 would appear in C2. If a S was in B3, then 8 would appear in C3 etc.



In the other spreadsheet, I have a slightly different scenario I also need to rectify.

From B2 – B7 I have empty cells again, to be able to enter either a Y or N. But in cell B6 ONLY, there is also an option to enter N/A along with a Y or N.
What I need to do in column C is similar to the first problem above. I need C2 – C7 to populate based on what the entered letters in column B’s value is.

This is where it gets tricky – If B6 doesn’t have N/A entered into it (so has Y or N), any cell in column B with a Y will have a value of 16.7. But if B6 has N/A entered into it, then any cell in column B with a Y is worth 20.

Once again, any cell that has a letter N in column B, it should be reflected with a zero in the corresponding cell in column C.
So basically, if there is an N/A in B6 then Y is worth 20 for all cells. If B6 doesn’t have an N/A then Y is worth 16.7 for all cells. N is worth 0 either way, whether there is an N/A in play or not.

I don't want to use a macro for this.

Thanks!
Rooboyz

[martindwilson]

first bit
=if(LOOKUP(B2,{"n","s","y"},{0,8,20})
2nd bit
what value do you want in c6 when b6 says n/a?
if its 0 then
=IF(AND(B2="y",$B$6<>"n/a"),16.7,LOOKUP(B2,{"n","y"},{0,20}))

[rooboyz]

first bit
=if(LOOKUP(B2,{"n","s","y"},{0,8,20})
2nd bit
what value do you want in c6 when b6 says n/a?
if its 0 then
=IF(AND(B2="y",$B$6<>"n/a"),16.7,LOOKUP(B2,{"n","y"},{0,20}))

The 2nd bit works perfect, thank you!

The 1st bit isn't working, it is needing the rest of the IF statement to be completed with the true and false part. Along with the closing bracket.

[martindwilson]

oops drop the if i was trying the second out at the same time
=LOOKUP(B2,{"n","s","y"},{0,8,20})
but maybe you need
if(b2="","",LOOKUP(B2,{"n","s","y"},{0,8,20}))

[rooboyz]

oops drop the if i was trying the second out at the same time
=LOOKUP(B2,{"n","s","y"},{0,8,20})
but maybe you need
if(b2="","",LOOKUP(B2,{"n","s","y"},{0,8,20}))

Thanks for your help on this! i didn't reply last time. But i just added to your reputation. Thanks appreciate your help.