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]

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

Don't quote numbers...

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

 

[VoG]

Try

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

 

[T. Valko]

Don't quote numbers...

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

If you have more than a "few" variables then you can use a table and a lookup function:

http://contextures.com/xlFunctions02.html

 

[MrKowz]

If you have more than a "few" variables then you can use a table and a lookup function:

http://contextures.com/xlFunctions02.html

Or, if you cannot have extra tables, you can hardcode a LOOKUP or INDEX/MATCH function to eliminate the need for nested IFs:

=INDEX(Sheet2!B5:B7,H5)
=LOOKUP(H5,{1,2,3},{Sheet2!B5,Sheet2!B6,Sheet2!B7})

 

[T. Valko]

Or, if you cannot have extra tables, you can hardcode a LOOKUP or INDEX/MATCH function to eliminate the need for nested IFs:


=LOOKUP(H5,{1,2,3},{Sheet2!B5,Sheet2!B6,Sheet2!B7})

Can't use cell references inside an array constant:

=LOOKUP(H5,{1,2,3},Sheet2!B5:B7)

Note that any number >3 will return Sheet2!B7.

 

[MrKowz]

Can't use cell references inside an array constant.

Thanks for catching that, I've been out of the loop too long.

 

[szachary]

Try

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

Excellent, this worked very well!! Thank you.

 

[T. Valko]

Thanks for catching that, I've been out of the loop too long.

Or:

I've been in the loop too long!

 

[szachary]

If you have more than a "few" variables then you can use a table and a lookup function:

http://contextures.com/xlFunctions02.html

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