Return value from two tables based on certain criteria

jigoro

New Member
Joined
Feb 26, 2010
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have three tables

Band 1Percentage
£ 150,0000.50%
£ 250,0001.00%
£ 350,0001.50%
£ 450,0002.00%
£ 600,0002.50%
£ 1,000,000


Band 2Percentage
£ 2,500,0000.5%
£ 2,750,0001.0%
£ 3,000,0001.5%
£ 3,250,0002.0%
£ 3,500,0002.5%
£ 4,000,000


Column1Column2
Band 1£ 155,000
Band 2£ 3,152,000
Percentage


The first two tables contain bandings greater or less than the next band level, so if the relevant value for band 1 is 155,000 the percentage would be 0.5%. I need a formula in the last table that returns the relevant percentage figures from the other two tables combined based on the figures against Band 1 or Band 2 in this third table. i.e. if Band 1 is 155,000 and Band 2 is £3,152,000 it would return 2%.

Any and all help greatly appreciated as always.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Use Column G formula & then Sum. Or only Use F7 formula.

Mail Generator2.xlsm
ABCDEFG
1Band 1Percentage
2150,0000.5%
3250,0001.0%
4350,0001.5%Column1Column2
5450,0002.0%Band 11550000.5%
6600,0002.5%Band 231520001.5%
71,000,000Percentage2.0%2.0%
8
9Band 2Percentage
102,500,0000.5%
112,750,0001.0%
123,000,0001.5%
133,250,0002.0%
143,500,0002.5%
154,000,000
Sheet2
Cell Formulas
RangeFormula
G5G5=VLOOKUP(F5,$A$2:$B$7,2,TRUE)
G6G6=VLOOKUP(F6,$A$10:$B$15,2,TRUE)
F7F7=VLOOKUP(F5,$A$2:$B$7,2,TRUE)+VLOOKUP(F6,$A$10:$B$15,2,TRUE)
G7G7=SUM(G5:G6)
 
Upvote 0
Another option, if you give your first 2 tables a name like Band_1 & Band_2
+Fluff v2.xlsm
ABCDEFG
1Band 1Percentage
21500000.005
32500000.01
43500000.015Column1Column2
54500000.02Band 11550000.005
66000000.025Band 231520000.015
71000000Percentage0.02
8
9Band 2Percentage
1025000000.005
1127500000.01
1230000000.015
1332500000.02
1435000000.025
154000000
16
Main
Cell Formulas
RangeFormula
G5G5=VLOOKUP(F5,Band_1,2,1)
G6G6=VLOOKUP(F6,Band_2,2,1)
F7F7=VLOOKUP(F5,INDIRECT(SUBSTITUTE(E5," ","_")),2,1)+VLOOKUP(F6,INDIRECT(SUBSTITUTE(E6," ","_")),2,1)
Named Ranges
NameRefers ToCells
Band_1=Main!$A$1:$B$7G5
Band_2=Main!$A$9:$B$15G6


Ignore the formulae in G5:G6 they are irrelevant.
 
Upvote 0
Solution
Another option, if you give your first 2 tables a name like Band_1 & Band_2
+Fluff v2.xlsm
ABCDEFG
1Band 1Percentage
21500000.005
32500000.01
43500000.015Column1Column2
54500000.02Band 11550000.005
66000000.025Band 231520000.015
71000000Percentage0.02
8
9Band 2Percentage
1025000000.005
1127500000.01
1230000000.015
1332500000.02
1435000000.025
154000000
16
Main
Cell Formulas
RangeFormula
G5G5=VLOOKUP(F5,Band_1,2,1)
G6G6=VLOOKUP(F6,Band_2,2,1)
F7F7=VLOOKUP(F5,INDIRECT(SUBSTITUTE(E5," ","_")),2,1)+VLOOKUP(F6,INDIRECT(SUBSTITUTE(E6," ","_")),2,1)
Named Ranges
NameRefers ToCells
Band_1=Main!$A$1:$B$7G5
Band_2=Main!$A$9:$B$15G6


Ignore the formulae in G5:G6 they are irrelevant.
Great, that's worked perfectly thanks
 
Upvote 0
Glad we could help & thanks for the feedback.

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0

Forum statistics

Threads
1,213,560
Messages
6,114,309
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top