See what range a number is in

[Salu]

Hi apologies if this has been answered before. I did search but I'm not sure of the terms to search for...

I want to look at a number and see what range it is in then in an adjacent column put a value.

Ranges are..

AN AM
Up to 20 2
21 to 40 3
41 to 60 4
61 plus 5

What I want to see is

AI AJ AK
72 £360.00 5
0 £0.00 0
23 £69.00 3
0 £0.00 0
0 £0.00 0
48 £192.00 4
37 £148.00 4

So what formula do I put in column AK to lookup the value in Column AI in the above ranges and return the value in column AM

many thanks

 

[Salu]

Sorry the column spacing hasn't come out right

 

[Yahya]

you need to modify your table to become like this

Excel Workbook
	AI	AJ	AK	AL	AM	AN
1	72	360	5		0	0
2	0	0	0		1	2
3	23	69	3		20	3
4	0	0	0		40	4
5	0	0	0		60	5
6	48	192	4
7	37	148	3
Sheet7

Excel 2007

Cell Formulas
Range	Formula
AK1	=LOOKUP(AI1,$AM$1:$AN$5)

 

[Salu]

Thanks that worked great.

 

[Yahya]

Thanks that worked great.

you are welcome