Help Please

[PhilWong]

How do I write a formula for the yellow box that will return a category in column D from the spend value.

	Spend		Category
	£5,323.24
A	B	C	D
1	£0	>=	1
2	£2,500	>=	1
3	£2,501	>=	2
4	£5,000	>=	2
5	£5,001	>=	3
6	£10,000	>=	3
7	£10,001	>=	4
8	£25,000	>=	4
9	£25,001	>=	5
10	£50,000	>=	5
11	£50,001	>=	6
12	£100,000	>=	6
13	£100,001	>=	7

<colgroup><col width="33" style="width: 25pt; mso-width-source: userset; mso-width-alt: 1206;"> <col width="75" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="3"> <tbody> </tbody>

 

[jproffer]

Try this:

=VLOOKUP(B1,B2:D14,3,TRUE)

 

[PhilWong]

Try this:

=VLOOKUP(B1,B2:D14,3,TRUE)

Thank you it works a dream.

 

[jproffer]

Happy to help, glad it worked. Thanks for the feedback.

Just as a side note, it's the "TRUE" argument that is important in your case. Approximate match = nearest but not greater than. If you change the order of your table, it will give you a result you're not expecting. If you don't use FALSE for the last argument, your table MUST be sorted A->Z (or low to high, however you want to look at it).

For instance, this:

Book1
	B	C	D
1	5323.24		3
2	0	>=	1
3	2500	>=	1
4	2501	>=	2
5	5000	>=	2
6	5001	>=	3
7	10000	>=	3
8	10001	>=	4
9	25000	>=	4
10	25001	>=	5
11	50000	>=	5
12	50001	>=	6
13	100000	>=	6
14	100001	>=	7
Sheet1

Cell Formulas
Range	Formula
D1	=VLOOKUP(B1,B2:D14,3,TRUE)

gives the correct result, while this:

Book1
	B	C	D
1	5323.24		#N/A
2	100001	>=	7
3	100000	>=	6
4	50001	>=	6
5	50000	>=	5
6	25001	>=	5
7	25000	>=	4
8	10001	>=	4
9	10000	>=	3
10	5001	>=	3
11	5000	>=	2
12	2501	>=	2
13	2500	>=	1
14	0	>=	1
Sheet1

Cell Formulas
Range	Formula
D1	=VLOOKUP(B1,B2:D14,3,TRUE)

returns an error.

 

[jproffer]

And by the way, if you DO use "FALSE" as the last argument...in your case, you won't get a result at all. It will be looking for 5323.24 and won't find it...and so will result in an error.

Book1
	B	C	D
1	5323.24		#N/A
2	0	>=	1
3	2500	>=	1
4	2501	>=	2
5	5000	>=	2
6	5001	>=	3
7	10000	>=	3
8	10001	>=	4
9	25000	>=	4
10	25001	>=	5
11	50000	>=	5
12	50001	>=	6
13	100000	>=	6
14	100001	>=	7
Sheet1

Cell Formulas
Range	Formula
D1	=VLOOKUP(B1,B2:D14,3,FALSE)