Question Regarding IF Commands

szachary

New Member
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,"")

Last edited:

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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,"")))

Try

=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

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

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.

Can't use cell references inside an array constant.

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

Try

=IF(H5=1,Sheet2!B5,IF(H5=2,Sheet2!B6,IF(H5=3,Sheet2!B7,"")))
Excellent, this worked very well!! Thank you.

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

I've been in the loop too long!

Replies
6
Views
290
Replies
9
Views
290
Replies
5
Views
246
Replies
3
Views
151
Replies
2
Views
246

1,196,131
Messages
6,013,633
Members
441,777
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back