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.

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

 

[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

[TABLE="width: 226"]
<tbody>[TR]
[TD="align: right"]-1000000[/TD]
[TD] <$0.00[/TD]
[/TR]
[TR]
[TD="align: right"]0[/TD]
[TD] $0.00-$499.99[/TD]
[/TR]
[TR]
[TD="align: right"]500[/TD]
[TD] $500.00 -$999.99[/TD]
[/TR]
[TR]
[TD="align: right"]1000[/TD]
[TD] $1000.00-$1499.99[/TD]
[/TR]
[TR]
[TD="align: right"]1500[/TD]
[TD] Above $1500.00[/TD]
[/TR]
</tbody>[/TABLE]


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