Assign percentage to values in table1 based on percenatage in table2

[bmbalamurali]

Hi,


I have two sets of table, one with few rows and its corresponding percentages (This table varies time to time i.e. number of rows, percentage etc),
The other table has fixed 11 rows with percentage values.


Illustration below:


Table1 (Dynamic)


500,000 - 10%
1,000,000 - 15%
2,000,000 - 15%
999,999,999 - 20%


Table2 (Fixed)


99999 -
249999 -
499999 -
999999 -
1999999 -
4999999 -
9999999 -
24999999 -
49999999 -
99999999 -
999999999 -


I need a formula (preferable) or VBA to have the table 2 assigned with percentage based on first table everytime.


in Table1, 500,000 - 10% , so the table2 should be filled with 10% till three rows
(because till 499,999 is less than 500,000)


in Table1, 1,000,000 - 15%, so the percentage in table2 for 4th row should be 15%
(because the 999,999 less than 1,000,000)


in Table1, 2,000,000 - 15%, so the percentage in table2 for 5th row should be 15%
(because the 1,999,999 less than 2,000,000)


in Table1 999,999,999 - 20%, so the percentage in table2 for rest of rows should be 20%
(because all other rows are less than 999,999,999)


Like wise the table 1 varies in row count and percentage, so each time the second table should be updated based on table1.




Below the expected solution


Table1 (Dynamic)


500,000 - 10%
1,000,000 - 15%
2,000,000 - 15%
999,999,999 - 20%


Table2 (Fixed)


99999 - 10%
249999 - 10%
499999 - 10%
999999 - 15%
1999999 - 15%
4999999 - 20%
9999999 - 20%
24999999 - 20%
49999999 - 20%
99999999 - 20%
999999999 -20%


Hope i explined my requirement clearly, please do come back if need more information.


Thanks,
Bala

 

[AlanY]

try this

Excel 2013/2016
	A	B
1	0	10.0%
2	500,000	15.0%
3	1,000,000	15.0%
4	2,000,000	20.0%
5	999,999,999	20.0%
6
7
8	99,999	10.0%
9	249,999	10.0%
10	499,999	10.0%
11	999,999	15.0%
12	1,999,999	15.0%
13	4,999,999	20.0%
14	9,999,999	20.0%
15	24,999,999	20.0%
16	49,999,999	20.0%
17	99,999,999	20.0%
18	999,999,999	20.0%
Sheet1

Cell Formulas
Range	Formula
B8	=LOOKUP(A8,$A$1:$A$5,$B$1:$B$5)