Name box in if formula

[gergo1001]

Hi All,

I have the below formula, and the coloured words are name boxes. What I try to achive here is to have a shorter formula which pulls the info from those ranges, instead of adding each column to the formula.

=
IF(U5=HighestRatesTech,580,
IF(U5=MidHighRatesTech,475,
IF(U5=Z19,370,
IF(U5=LowRatesTech,AA20,0))))

Thanks for the help.

 

[Scott Huish]

Instead of having named ranges, build a lookup table and use VLOOKUP instead.

Something like this:

Excel 2010
	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
2	Highest RatesTech	1	580
3	MidHighRatesTech	2	475
4	LowRatesTech	3	46
5	Z19	42	370										42	370
Sheet1

Cell Formulas
Range	Formula
K4	=AA220
J5	=Z19
V5	=IFERROR(VLOOKUP(U5,$J$2:$K$5,2,0),0)