Excel: #VALUE! Error on Index Function


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:

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


This question generated 16 answers. To proceed to the answers, click here.

This thread is current as of June 03, 2015.


For more resources for Microsoft Excel