I am using an Index/Match Function on a named range lookup, and I am getting a Value error, but in the Function Arguments tool, it returns everything correctly, other than the result.
My Function is: =INDEX(VLOOKUP($D$2,Section,2,FALSE),MATCH(B10,Duration,0),5)
The Array VLookup correctly returns Sec_5
The Row_num Match correctly returns 5
The Column_num shows as 5 (4-MO)
Thus, I would expect the result to be: 1.67
But, I get the #VALUE! error.
In range (named Sec_5) I have the following values:
<colgroup><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>
My Function is: =INDEX(VLOOKUP($D$2,Section,2,FALSE),MATCH(B10,Duration,0),5)
The Array VLookup correctly returns Sec_5
The Row_num Match correctly returns 5
The Column_num shows as 5 (4-MO)
Thus, I would expect the result to be: 1.67
But, I get the #VALUE! error.
In range (named Sec_5) I have the following values:
Section | Duration | 2-MO | 3-MO | 4-MO | 6-MO | 9-MO | 1-YR | 2-YR | 5-YR | 10-YR | 25-YR | 50-YR | 100-YR |
5 | 10-DAY | 2.2 | 2.64 | 3.05 | 3.58 | 4.12 | 4.48 | 5.2 | 6.22 | 7.22 | 8.61 | 9.66 | 10.88 |
5 | 5-DAY | 1.76 | 2.11 | 2.39 | 2.77 | 3.18 | 3.46 | 4.05 | 4.94 | 5.72 | 6.92 | 7.98 | 9.18 |
5 | 72-HR | 1.51 | 1.77 | 2 | 2.32 | 2.67 | 2.9 | 3.47 | 4.41 | 5.16 | 6.22 | 7.06 | 8.12 |
5 | 48-HR | 1.4 | 1.64 | 1.82 | 2.11 | 2.43 | 2.64 | 3.13 | 3.93 | 4.67 | 5.75 | 6.52 | 7.33 |
5 | 24-HR | 1.31 | 1.52 | 1.67 | 1.93 | 2.19 | 2.38 | 2.91 | 3.64 | 4.27 | 5.15 | 5.87 | 6.61 |
5 | 18-HR | 1.23 | 1.43 | 1.57 | 1.81 | 2.06 | 2.24 | 2.74 | 3.42 | 4.01 | 4.84 | 5.52 | 6.21 |
5 | 12-HR | 1.14 | 1.32 | 1.45 | 1.68 | 1.9 | 2.07 | 2.53 | 3.17 | 3.71 | 4.48 | 5.11 | 5.75 |
5 | 6-HR | 0.98 | 1.15 | 1.25 | 1.45 | 1.65 | 1.79 | 2.18 | 2.73 | 3.2 | 3.86 | 4.4 | 4.96 |
5 | 3-HR | 0.84 | 0.97 | 1.06 | 1.23 | 1.4 | 1.52 | 1.86 | 2.33 | 2.73 | 3.3 | 3.76 | 4.23 |
5 | 2-HR | 0.76 | 0.88 | 0.97 | 1.12 | 1.27 | 1.38 | 1.69 | 2.11 | 2.48 | 2.99 | 3.4 | 3.83 |
5 | 1-HR | 0.62 | 0.72 | 0.78 | 0.91 | 1.03 | 1.12 | 1.37 | 1.71 | 2.01 | 2.42 | 2.76 | 3.11 |
5 | 30-MIN | 0.48 | 0.56 | 0.62 | 0.71 | 0.81 | 0.88 | 1.08 | 1.35 | 1.58 | 1.91 | 2.17 | 2.45 |
5 | 15-MIN | 0.35 | 0.41 | 0.45 | 0.52 | 0.59 | 0.64 | 0.79 | 0.98 | 1.15 | 1.39 | 1.58 | 1.78 |
5 | 10-MIN | 0.28 | 0.32 | 0.35 | 0.41 | 0.46 | 0.5 | 0.61 | 0.76 | 0.9 | 1.08 | 1.23 | 1.39 |
5 | 5-MIN | 0.16 | 0.19 | 0.2 | 0.23 | 0.27 | 0.29 | 0.35 | 0.44 | 0.51 | 0.62 | 0.7 | 0.79 |
<colgroup><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>