Look Up Formula for Range

[kumar1803]

Hello,

I have column where i have some negative values and positive values. I would like to use a LOOKUP formula to put them in a range. My range is as follows.

• < $0.00
• $0.00-$499.99
• $500.00 -$999.99
• $1000.00-$1499.99
• Above $1500.00

 

[etaf]

what do you mean put " put them in "
do you want to count them?

can you provide more of an example of how the data looks and how you want the final outcome to look

 

[kumar1803]

Please see data below: I need the last column for LOOKUP value in the range i have provided earlier.

Contract Date	Stock Number	Make	Model	Year Model	Deal Type	Days On Lot	Vehicle Category	Total Inventory Cost	Sales Price	Lender Acquisition Fee	Sales Gross Profit	Profit ( Front)
11/25/2015	2855	Ford	Expedition	2012	F	198	Regular SUV	$ 27,476.97	$ 21,988.00	$ 99.00	$ (5,587.97)	$ (5,488.97)
11/11/2015	2884	Ford	Expedition	2011	F	173	Regular SUV	$ 24,541.79	$ 20,988.00	$ 95.00	$ (3,648.79)	$ (3,553.79)
11/16/2015	2979	RAM	1500	2014	F	130	Regular Truck	$ 37,745.25	$ 34,988.00	$ 50.00	$ (2,807.25)	$ (2,757.25)
11/7/2015	2903	Toyota	Tundra	2008	F	156	Leveled truck	$ 23,102.55	$ 21,488.00	$ -	$ (1,614.55)	$ (1,614.55)
11/9/2015	2986	Ford	F-350 SD	2012	C	122	Commercial	$ 28,570.99	$ 27,125.00	$ -	$ (1,445.99)	$ (1,445.99)
11/20/2015	3025	Chevrolet	Tahoe	2012	F	113	Regular SUV	$ 28,846.29	$ 27,488.00	$ 50.00	$ (1,408.29)	$ (1,358.29)
11/13/2015	2658B	Ford	F-150	2014	C	112	Regular Truck	$ 36,439.70	$ 35,188.00	$ -	$ (1,251.70)	$ (1,251.70)
11/16/2015	2781A	Ford	F-150	2011	F	106	Leveled Truck	$ 23,990.59	$ 22,988.00	$ -	$ (1,002.59)	$ (1,002.59)
11/11/2015	2883	Jeep	Wrangler	2012	F	174	Lifted SUV	$ 28,955.93	$ 27,988.00	$ 95.00	$ (1,062.93)	$ (967.93)
11/17/2015	2970	Ford	F-450 SD	2011	C	137	Commercial	$ 28,649.69	$ 27,838.00	$ -	$ (811.69)	$ (811.69)
11/20/2015	3017	Toyota	Tundra	2012	F	120	Lifted Truck	$ 29,739.20	$ 28,988.00	$ 295.00	$ (1,046.20)	$ (751.20)
11/5/2015	3001	RAM	1500	2013	F	112	Leveled Truck	$ 23,419.95	$ 22,988.00	$ 150.00	$ (581.95)	$ (431.95)
11/9/2015	2953	Jeep	Wrangler	2015	F	133	Lifted SUV	$ 41,553.83	$ 41,988.00	$ -	$ (65.83)	$ (65.83)
11/5/2015	3116B	Lexus	RX 330	2004	C	7	Regular SUV	$ 3,500.00	$ 3,500.00	$ -	$ -	$ -
11/23/2015	3039	Chevrolet	Silverado 3500HD	2015	F	109	Commercial	$ 50,596.04	$ 50,688.00	$ -	$ 91.96	$ 91.96
11/14/2015	3060	Chevrolet	Suburban	2012	C	87	Regular SUV	$ 32,204.71	$ 32,791.80	$ -	$ 587.09	$ 587.09
11/10/2015	3067	Chevrolet	Silverado 1500	2015	F	77	Regular Truck	$ 45,522.47	$ 45,788.00	$ -	$ 765.53	$ 765.53
11/25/2015	2984	Chevrolet	Silverado 1500	2012	F	138	Lifted Truck	$ 23,660.80	$ 24,488.00	$ -	$ 827.20	$ 827.20
11/7/2015	2920A	Ford	F-150	2012	F	120	Regular Truck	$ 22,107.99	$ 22,988.00	$ -	$ 880.01	$ 880.01
11/12/2015	3068	Jeep	Wrangler	2014	F	79	Lifted SUV	$ 39,063.92	$ 39,988.00	$ 95.00	$ 829.08	$ 924.08
11/6/2015	3059	Jeep	Wrangler	2012	F	80	Lifted SUV	$ 29,007.83	$ 29,988.00	$ -	$ 980.17	$ 980.17
11/2/2015	3054	Chevrolet	Silverado 1500	2014	F	80	Lifted Truck	$ 38,755.22	$ 39,988.00	$ 50.00	$ 1,182.78	$ 1,232.78
11/16/2015	3074	RAM	2500	2015	F	80	Lifted Truck	$ 50,550.76	$ 51,988.00	$ -	$ 1,437.24	$ 1,437.24
11/3/2015	3018	Ford	F-350 SD	2013	F	103	Commercial	$ 24,840.22	$ 26,300.00	$ -	$ 1,459.78	$ 1,459.78
11/27/2015	3052A	Dodge	Durango	2011	F	34	Reular SUV	$ 17,518.75	$ 18,988.00	$ 495.00	$ 974.25	$ 1,469.25
11/16/2015	3132	Chevrolet	Silverado 1500	2014	F	30	Regular Truck	$ 36,703.03	$ 38,235.45	$ 199.00	$ 1,333.42	$ 1,532.42
11/10/2015	2817A	Jeep	Wrangler	2012	F	96	Lifted SUV	$ 29,449.60	$ 30,988.00	$ -	$ 1,538.40	$ 1,538.40
11/3/2015	3049	RAM	3500	2014	F	84	Leveling Truck	$ 45,313.11	$ 46,988.00	$ -	$ 1,674.89	$ 1,674.89
11/16/2015	3064	Dodge	Ram 2500	2009	F	87	Leveled Truck	$ 23,207.55	$ 24,988.00	$ -	$ 1,780.45	$ 1,780.45
11/3/2015	2966	Jeep	Wrangler	2014	F	124	Lifted SUV	$ 32,188.11	$ 33,988.00	$ -	$ 1,799.89	$ 1,799.89

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[etaf]

you could use a lookup or a nested iF

• < $0.00
• $0.00-$499.99
• $500.00 -$999.99
• $1000.00-$1499.99
• Above $1500.00

PUT the list as follows

-1000000	<$0.00
0	$0.00-$499.99
500	$500.00 -$999.99
1000	$1000.00-$1499.99
1500	Above $1500.00

<tbody>
</tbody>

Then use
=VLOOKUP(J2,Sheet2!$A$2:$B$6,2,TRUE)

Assuming the table is in A2:B6 on sheet2

see attcahed
https://www.dropbox.com/s/ym19py4zk1pom01/lookupRange.xlsx?dl=0