Spreadsheet: 159398d1338614722-excel-2007-data-validation-with-multiple-references-example
Sheet: Formulas

State | Age | Commission % | [NAME] |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Life | Policy Type | Age |  |  | Annuity
ID | 0 | 1 | 1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Allianz | Term |  |  |  | Allianz
OR | 1 | 0.99 | 0.95
WA | 2 | 0.98 | 0.9 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 10 yr | 18-65 | 0.369 |  | American Equity
 | 3 | 0.97 | 0.85
Policy Type | 4 | 0.96 | 0.8 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 20 yr | 18-65 | 0.495 |  | Americo
 | 5 | 0.95 | 0.75
Annuity | 6 | 0.94 | 0.7 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 30 yr | 18-50 | 0.495 |  | Aviva
Life | 7 | 0.93 | 0.65
LTC | 8 | 0.92 | 0.6 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | SPWL | 50-69 |  |  | Great American Life Insurance Company
MedSupp | 9 | 0.91 | 0.55
 | 10 | 0.9 | 0.5 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 70-79 |  |  | Life of the Southwest
Splitting Agent | 11 | 0.89 | 0.45
 | 12 | 0.88 | 0.4 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | 80-85 |  |  | National Western
[NAME] | 13 | 0.87 | 0.35
[NAME] | 14 | 0.86 | 0.3 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | North American Company
[NAME] | 15 | 0.85 | 0.25
D. Jones | 16 | 0.84 | 0.2 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | One America
[NAME] | 17 | 0.83 | 0.15
[NAME] | 18 | 0.82 | 0.1 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | Royal Neighbors of America
[NAME] | 19 | 0.81 | 0.05
P. Young | 20 | 0.8 | 0
[NAME] | 21 | 0.79
[NAME] | 22 | 0.78
T. Rafferty | 23 | 0.77
[NAME] | 24 | 0.76
Team | 25 | 0.75
 | 26 | 0.74
Life Type | 27 | 0.73
 | 28 | 0.72
Final Expense | 29 | 0.71
WL | 30 | 0.7
TL | 31 | 0.69
UL | 32 | 0.68
SPWL | 33 | 0.67
 | 34 | 0.66
 | 35 | 0.65
Annuity Type | 36 | 0.64
 | 37 | 0.63
Flex | 38 | 0.62
Indexed | 39 | 0.61
Fixed | 40 | 0.6
 | 41 | 0.59
Modal Premium | 42 | 0.58
 | 43 | 0.57
12 | 44 | 0.56
4 | 45 | 0.55
2 | 46 | 0.54
1 | 47 | 0.53
 | 48 | 0.52
 | 49 | 0.51
 | 50 | 0.5
 | 51 | 0.49
 | 52 | 0.48
 | 53 | 0.47
 | 54 | 0.46
 | 55 | 0.45
 | 56 | 0.44
 | 57 | 0.43
 | 58 | 0.42
 | 59 | 0.41
 | 60 | 0.4
 | 61 | 0.39
 | 62 | 0.38
 | 63 | 0.37
 | 64 | 0.36
 | 65 | 0.35
 | 66 | 0.34
 | 67 | 0.33
 | 68 | 0.32
 | 69 | 0.31
 | 70 | 0.3
 | 71 | 0.29
 | 72 | 0.28
 | 73 | 0.27
 | 74 | 0.26
 | 75 | 0.25
 | 76 | 0.24
 | 77 | 0.23
 | 78 | 0.22
 | 79 | 0.21
 | 80 | 0.2
 | 81 | 0.19
 | 82 | 0.18
 | 83 | 0.17
 | 84 | 0.16
 | 85 | 0.15
 | 86 | 0.14
 | 87 | 0.13
 | 88 | 0.12
 | 89 | 0.11
 | 90 | 0.1
 | 91 | 0.09
 | 92 | 0.08
 | 93 | 0.07
 | 94 | 0.0625
 | 95 | 0.06
 | 96 | 0.05
 | 97 | 0.04
 | 98 | 0.03
 | 99 | 0.02
 | 100 | 0.01
 | 101 | 0
 | 102
 | 103
 | 104
 | 105
 | 106
 | 107
 | 108
 | 109
 | 110