Question Regarding IF Commands

[szachary]

Hey all,

i am trying to get a simple numberical list of reason codes (1-4) to populate the adjacent cell with the explanation of the unique reason code entered.

For example, if a user in A1 enters the number "1", then the corresponding field A2 will generate the reason code name "Material Damaged". I have the following IF Command written, but I think I have my quotations mixed up? Any help would be greatly appreciated, thanks!!

=IF(H5="1",Sheet2!B5,IF(H5="2",Sheet2!B6,),IF(H5="3",Sheet2!B7"")

In the above formula, the numerous references to sheet 2 are pulling the reason codes I have predefined. I know there is a cleared way to simply specify the reason codes within the formula, I am just unable to get that to work. I can get the following formula to work, but its the third and fourth variables that screw me up:

=IF(H5="1",Sheet2!B5,IF(H5="2",Sheet2!B6,"")

 

[T. Valko]

I didn't know VLOOKUPS could be nested in IF Commands....excellent resource, thanks a bunch!

Well, you wouldn't nest the lookup with an IF, you'd use the lookup instead of nesting a bunch of IFs.

 

[szachary]

Quick question so I know for next time, how do I close this arguement. I see multiple quotations and closed brackets at the end of the command. If I wanted to add a fourth or fifth variable here, how would I insert it and close it?

 

[T. Valko]

Quick question so I knwo for next time, how do I close this arguement. I see multiple quotations and closed brackets at the end of the command. If I wanted to add a fourth or fifth variable here, how would I inster it and close it?

The last set of quotes will cause the formula to return a blank if none of the other conditions are met.

=IF(H5=1,Sheet2!B5,IF(H5=2,Sheet2!B6,IF(H5=3,Sheet2!B7,"")))

If H5 does not contain 1, 2 or 3 then the formula will return a blank.

When using formulas like this you should have in mind what result you want when none of the conditions is met if that might be possible.

To add more conditions just follow the pattern.

 

[szachary]

That's what I thought, but when I created the formula below it gives me a REF#:

=IF(H5=1,Sheet2!B5,IF(H5=2,Sheet2!B6,IF(H5=3,Sheet2!B7,IF(H5=4,Sheet2!B8,""))))

 

[T. Valko]

That's what I thought, but when I created the formula below it gives me a REF#:

=IF(H5=1,Sheet2!B5,IF(H5=2,Sheet2!B6,IF(H5=3,Sheet2!B7,IF(H5=4,Sheet2!B8,""))))

That formula is syntactically correct and should not return an error unless one of the referenced cells already contains an error.