Lookup a value within a range

[lt]

I wanted to lookup the Anuual Lease (column 2 below) base on the
automobiles FMV (column 1 below). So for example if the FMV is 3,500
the Annual Lease should return 1,350. How can I do this?

(1) Automobile FMV (2) Annual Lease
$0 to 999 $ 600
1,000 to 1,999 850
2,000 to 2,999 1,100
3,000 to 3,999 1,350
4,000 to 4,999 1,600
5,000 to 5,999 1,850
6,000 to 6,999 2,100
7,000 to 7,999 2,350
8,000 to 8,999 2,600
9,000 to 9,999 2,850
10,000 to 10,999 3,100
11,000 to 11,999 3,350
12,000 to 12,999 3,600
13,000 to 13,999 3,850
14,000 to 14,999 4,100
15,000 to 15,999 4,350
16,000 to 16,999 4,600
17,000 to 17,999 4,850
18,000 to 18,999 5,100
19,000 to 19,999 5,350
20,000 to 20,999 5,600
21,000 to 21,999 5,850
22,000 to 22,999 6,100
23,000 to 23,999 6,350
24,000 to 24,999 6,600
25,000 to 25,999 6,850
26,000 to 27,999 7,250
28,000 to 29,999 7,750
30,000 to 31,999 8,250
32,000 to 33,999 8,750
34,000 to 35,999 9,250
36,000 to 37,999 9,750
38,000 to 39,999 10,250
40,000 to 41,999 10,750
42,000 to 43,999 11,250
44,000 to 45,999 11,750
46,000 to 47,999 12,250
48,000 to 49,999 12,750
50,000 to 51,999 13,250
52,000 to 53,999 13,750
54,000 to 55,999 14,250
56,000 to 57,999 14,750
58,000 to 59,999 15,250

 

[Jim Cone]

Separate the data into columns using Text to Columns - use a space as the delimiter.
You should end up with four columns.
Use the vLookup function on the first/fourth columns with True as the fourth argument.
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware

,
,
,

"lt" <[email protected]>
wrote in message
I wanted to lookup the Anuual Lease (column 2 below) base on the
automobiles FMV (column 1 below). So for example if the FMV is 3,500
the Annual Lease should return 1,350. How can I do this?

(1) Automobile FMV (2) Annual Lease
$0 to 999 $ 600
1,000 to 1,999 850
2,000 to 2,999 1,100
3,000 to 3,999 1,350
4,000 to 4,999 1,600
5,000 to 5,999 1,850
6,000 to 6,999 2,100
7,000 to 7,999 2,350
8,000 to 8,999 2,600
9,000 to 9,999 2,850
10,000 to 10,999 3,100
11,000 to 11,999 3,350
12,000 to 12,999 3,600
13,000 to 13,999 3,850
14,000 to 14,999 4,100
15,000 to 15,999 4,350
16,000 to 16,999 4,600
17,000 to 17,999 4,850
18,000 to 18,999 5,100
19,000 to 19,999 5,350
20,000 to 20,999 5,600
21,000 to 21,999 5,850
22,000 to 22,999 6,100
23,000 to 23,999 6,350
24,000 to 24,999 6,600
25,000 to 25,999 6,850
26,000 to 27,999 7,250
28,000 to 29,999 7,750
30,000 to 31,999 8,250
32,000 to 33,999 8,750
34,000 to 35,999 9,250
36,000 to 37,999 9,750
38,000 to 39,999 10,250
40,000 to 41,999 10,750
42,000 to 43,999 11,250
44,000 to 45,999 11,750
46,000 to 47,999 12,250
48,000 to 49,999 12,750
50,000 to 51,999 13,250
52,000 to 53,999 13,750
54,000 to 55,999 14,250
56,000 to 57,999 14,750
58,000 to 59,999 15,250

 

[Ms-Exl-Learner]

This is a simple mathematical calculation and no need of any helper
column or the Value Table to be referred. The below formula will do
the work.

Assume that your input cell is A1.

A1 cell
3,500

Copy and paste the below formula in B1 cell.
=IF(D3="","",IF(D3>=26000,((INT(FLOOR(D3,1000)/999)*250)+600)+150,
(INT(FLOOR(D3,1000)/999)*250)+600))

Input any value in A1 cell which will get you the result which is
given in your example data.

Change the cell reference A1 in the above formula to your desired
cell, if required.

Hope it's clear!

 

[Ms-Exl-Learner]

Correction:-
Change the cell reference D3 to A1 in my above formula like the below.

=IF(A1="","",IF(A1>=26000,((INT(FLOOR(A1,1000)/999)*250)+600)+150,
(INT(FLOOR(A1,1000)/999)*250)+600))

 

[Ms-Exl-Learner]

Correction:-
Change the cell reference D3 to A1 in my above formula like the below.

=IF(A1="","",IF(A1>=26000,((INT(FLOOR(A1,1000)/999)*250)+600)+150,
(INT(FLOOR(A1,1000)/999)*250)+600))

 

[chandra sekaran]

Correction:-
Change the cell reference D3 to A1 in my above formula like the below.

=IF(A1="","",IF(A1>=26000,((INT(FLOOR(A1,1000)/999)*250)+600)+150,
(INT(FLOOR(A1,1000)/999)*250)+600))

-----------------------
Ms-Exl-Learner
-----------------------









- Show quoted text -

hi i am chandru form chennai, india,
Sheet1
Colum A Column B Column C

1. 0 999 600
2. 1000 2999 1100
3. 3000 3999 1350


Sheet 2
Result
Colum A
1 3500 =VLOOKUP(A3,Sheet1!A1:C5,3)

Retun B1 1350

Chandru