IF Query - I think?

[broncos347]

I have a spreadsheet which hold the footfalls for a number of properties what I would like to do is apply a weighting score depending on where the footfall falls in the banding below.

Excel 2010
	G	H
10	Footfall band	footfall weighting
11
12	0 - 500,000	10
13	500,001 - 1,000,000	30
14	1,000,001 - 2,000,000	40
15	2,000,001 - 3,000,000	50
16	3,000,001 - 4,000,000	60
17	4,000,001 - 5,000,000	70
18	5,000,001 - 6,000,000	80
19	6,000,001 - 7,000,000	90
20	7,000,001 - 8,000,000	100
21	8,000,001 - 9,000,000	150
22	9,000,001 - 10,000,000	175
23	10,000,001 +	200
Misc

 

[MrKowz]

This looks like a perfect use of a VLOOKUP with the "TRUE" modifier.

Try:

Excel 2010
	A	B	C	D	E	F
1	Footfall Band	Footfall Weighting
2	0	10			Footfall Band	1235686
3	500001	30			Footfall Weighting	40
4	1000001	40
5	2000001	50
6	3000001	60
7	4000001	70
8	5000001	80
9	6000001	90
10	7000001	100
11	8000001	150
12	9000001	175
13	10000001	200
Sheet1

Cell Formulas
Range	Formula
F3	=VLOOKUP(F2,A2:B13,2,TRUE)