BigBeachBananas
Active Member
- Joined
- Jul 13, 2021
- Messages
- 450
- Office Version
- 365
- Platform
- Windows
- MacOS
Hi,
I'm looking for a way to looking values from multiple with 2 conditions from multiple tables. Please see the sample data set below:
I have a different table for each BAND, within each BAND TABLE, it's broken down by ISSUE AGE and Policy YEAR. What's the best way to look up those factor values?
I'm looking for a way to looking values from multiple with 2 conditions from multiple tables. Please see the sample data set below:
Catholic Order of Forresters.xlsx | |||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | |||
1 | ID | Issue Age | Policy Year | Band | Factor | Band1 | |||||||
2 | 153 | 25 | 1 | Band1 | 83% | Issue Age/PolicyYear | 1 | 2 | 3 | 4 | |||
3 | 101 | 20 | 3 | Band2 | 78% | 20 | 77.58% | 83.19% | 88.79% | 100.00% | |||
4 | 164 | 30 | 4 | Band3 | 100% | 25 | 83.19% | 88.79% | 100.00% | 100.00% | |||
5 | 30 | 88.79% | 100.00% | 100.00% | 100.00% | ||||||||
6 | |||||||||||||
7 | Band2 | ||||||||||||
8 | Issue Age/PolicyYear | 1 | 2 | 3 | 4 | ||||||||
9 | 20 | 79.7% | 79.4% | 79.2% | 78.9% | ||||||||
10 | 25 | 78.9% | 78.3% | 77.7% | 77.2% | ||||||||
11 | 30 | 78.1% | 77.2% | 76.3% | 75.4% | ||||||||
12 | |||||||||||||
13 | Band3 | ||||||||||||
14 | Issue Age/PolicyYear | 1 | 2 | 3 | 4 | ||||||||
15 | 20 | 75% | 73% | 71% | 100% | ||||||||
16 | 25 | 74% | 72% | 70% | 100% | ||||||||
17 | 30 | 74% | 72% | 70% | 100% | ||||||||
Sample |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H9:K11,H15:J17 | H9 | ='[Mortality Factors Tables - Blended 2020.xlsx]Blend Factor Table'!H9*'[Mortality Factors Tables - Blended 2020.xlsx]B5 (For APL)'!H10 |
I have a different table for each BAND, within each BAND TABLE, it's broken down by ISSUE AGE and Policy YEAR. What's the best way to look up those factor values?