Look up a value and return a range please?

[gian1]

Hi </SPAN>

New to the forum and hoping to draw upon expert tips.</SPAN>

I have a spreadsheet with a range of prices for services ranging from £50,000 to £50m and the values change every month</SPAN>

I want to create another column so I only show a range for the prices e.g. £75,000 should be shown as £50k to £100k, £2.4m should be shown as £2m-£5m</SPAN>

I did post below but can only have 7 statements I believe, - </SPAN>
=IF(AND(G93>0,G93<100),"£50k-£100k",IF(AND(G93>=100,G93<250),"£100k-250k",IF(AND(G93>=250,G93<1000),"£500k-£1m",IF(AND(G93>=1000,G93<2000),"£1m-£2m",IF(AND(G93>=2000,G93<5000),"£2m-£5m",IF(AND(G93>=5000,G93<10000),"£5m-£10m",IF(AND(G93>=10000,G93<20000),"£10m-£20m")))))))</SPAN>

I require 11 range value – any ideas, look up somehow?</SPAN>

Much Appreciated</SPAN>

 

[Andrew Poulsom]

Welcome to MrExcel.

Make a table like this:

Sheet3

*	A	B
88	0	£50k-£100k
89	100	£100k-250k
90	250	£500k-£1m
91	1000	£1m-£2m
92	2000	£2m-£5m
93	5000	£5m-£10m
94	10000	£10m-£20m

<colgroup><col style="width: 30px; font-weight: bold;"><col style="width: 42px;"><col style="width: 75px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie Html" target="_blank"> Excel Jeanie HTML 4

Then use eg:

=VLOOKUP(G93,A1:B7,2)