Formula in one cell to return number based on drop down selection in another cell.

[jwest510]

I got such great help earlier today, I thought I'd ask for another favor. Scouring for answers didn't seem to help. So my situation is, I've created a drop down in B1 that has 4 options. Each option has a records retention rate associated with it. I would like cell B2 to reflect that rate based on the option I choose in B1.

So for example: B1 says "Process", I would like B2 to show "3". If I changed B1 to say, Official, I would like B2 to show 7. If B1 says, Procedure, B2 should show 3.

Any thoughts?

 

[etaf]

you can use a nested if in B2 put

BUT did you want 3 in B2 for Process and for Procedure ?

you only have 3 options
Process = 3
Procedure = 3
Official = 7

=IF( B1 = "process" , 3 , IF( B1 = "official" , 7 , IF( etc
OR if the process / procedure is correct and you only have 3 values to show
then if its not process or procedure - it must be Official so thats a 7

=IF( OR( B1 = "procedure" , B1 = "process") , 3 , 7 )

lets us know exactly what can be in B1 and what results you need in B2

 

[jtakw]

Hi,

A couple of ways to consider:

Excel 2010
	A	B	C	D	E	F	G
1	Hardcoded	Procedure		From List	Official		Process
2		3			7		Official
3							Procedure
Sheet23

Cell Formulas
Range	Formula
B2	=LOOKUP(B1,{"Process","Official","Procedure"},{3,7,3})
E2	=LOOKUP(E1,G1:G3,{3,7,3})

Formula in B2 has your criteria hardcoded into the formula.
Formula in E2 uses your Drop Down list range as criteria, in my sample, the drop down list is G1:G3.

 

[jwest510]

Both work splendidly! Thank you neighbor!

 

[jtakw]

You're welcome, welcome to the forum.