Multiple If Then Statements

[JEllison]

Ok this is probably very simple. I have a set of function codes then I need to link back to check sort codes so I was thinking about using the if then fuction. Something like if c2 = 200 then return 111 if its 234 then return 112 if its 276 then return 113 and so on. First off is this the best function avialable for this type of thing, how many if then statements can be included in one formula? and how do you actually write the if then to include this.

Thanks

[JEllison]

ok I know how to nest the multiple if then now. But it looks like you can only nest 8 of them at a time. I have something closer to 80 that i need to enter. Any help would be appreciated.

Thanks,

[avwbugz]

Hi there,

I am facing the same issue here. it only limits me to 8. To all the Excel guru out there, please help!

[rylo]

Hi

Depending on your situation, the VLOOKUP function is probably the way to go. Build a table of the input value / output value and use VLOOKUP to extract your results.

From your example, I can't be sure if there is a one to one match between your input and output, or if you expect to see 111 for any number in the range 200 to 233.

HTH

rylo

[Teethless mama]

Ok this is probably very simple. I have a set of function codes then I need to link back to check sort codes so I was thinking about using the if then fuction. Something like if c2 = 200 then return 111 if its 234 then return 112 if its 276 then return 113 and so on. First off is this the best function avialable for this type of thing, how many if then statements can be included in one formula? and how do you actually write the if then to include this.

Thanks

Try this:

=LOOKUP(C2,{200,234,276},{111,112,113})

Adjust to suit

[avwbugz]

Here is my sample If function code:

=IF('Pre data entry'!B3="Education - Secondary",'Raw Data - Do not touch'!B3,IF('Pre data entry'!B3="Education - Ter",'Raw Data - Do not touch'!C3,IF('Pre data entry'!B3="Manufacturing & Energy",'Raw Data - Do not touch'!F3,IF('Pre data entry'!B3="Financial / Insurance",'Raw Data - Do not touch'!H3,IF('Pre data entry'!B3="Public / Government",'Raw Data - Do not touch'!D3,IF('Pre data entry'!B3="Health Care",'Raw Data - Do not touch'!G3,IF('Pre data entry'!B3="Retail & Consumer",'Raw Data - Do not touch'!E3,IF('Pre data entry'!B3="IT / Communications",'Raw Data - Do not touch'!I3,0))))))))

As you can see, there are 8 categories (industry), I need to add a few more which IF function only support up to 8. Is there a better way?