| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Lookup a value within a range
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup a value within a range
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" 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup a value within a range
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 ----------------------- On Jul 28, 12:08*am, lt wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup a value within a range
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 ----------------------- On Jul 29, 9:58*am, Ms-Exl-Learner wrote: 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 ----------------------- On Jul 28, 12:08*am, lt wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup a value within a range
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 ----------------------- On Jul 29, 9:58*am, Ms-Exl-Learner wrote: 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 ----------------------- On Jul 28, 12:08*am, lt wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Lookup a value within a range
On Jul 29, 10:03*am, Ms-Exl-Learner wrote:
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 ----------------------- On Jul 29, 9:58*am, Ms-Exl-Learner wrote: 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 ----------------------- On Jul 28, 12:08*am, lt wrote: 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- Hide quoted text - - 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup across a Range | Excel Worksheet Functions | |||
| Lookup within a range | Excel Worksheet Functions | |||
| help with lookup and range | Excel Worksheet Functions | |||
| Lookup in a range. | Excel Discussion (Misc queries) | |||
| Range Lookup | Excel Discussion (Misc queries) | |||
![lt[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
