#VALUE! Error on Index Function

[bigmyk2k]

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:
[TABLE="width: 806"]
<colgroup><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Section[/TD]
[TD]Duration[/TD]
[TD]2-MO[/TD]
[TD]3-MO[/TD]
[TD]4-MO[/TD]
[TD]6-MO[/TD]
[TD]9-MO[/TD]
[TD]1-YR[/TD]
[TD]2-YR[/TD]
[TD]5-YR[/TD]
[TD]10-YR[/TD]
[TD]25-YR[/TD]
[TD]50-YR[/TD]
[TD]100-YR[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]10-DAY[/TD]
[TD="align: center"]2.2[/TD]
[TD="align: center"]2.64[/TD]
[TD="align: center"]3.05[/TD]
[TD="align: center"]3.58[/TD]
[TD="align: center"]4.12[/TD]
[TD="align: center"]4.48[/TD]
[TD="align: center"]5.2[/TD]
[TD="align: center"]6.22[/TD]
[TD="align: center"]7.22[/TD]
[TD="align: center"]8.61[/TD]
[TD="align: center"]9.66[/TD]
[TD="align: center"]10.88[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5-DAY[/TD]
[TD="align: center"]1.76[/TD]
[TD="align: center"]2.11[/TD]
[TD="align: center"]2.39[/TD]
[TD="align: center"]2.77[/TD]
[TD="align: center"]3.18[/TD]
[TD="align: center"]3.46[/TD]
[TD="align: center"]4.05[/TD]
[TD="align: center"]4.94[/TD]
[TD="align: center"]5.72[/TD]
[TD="align: center"]6.92[/TD]
[TD="align: center"]7.98[/TD]
[TD="align: center"]9.18[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]72-HR[/TD]
[TD="align: center"]1.51[/TD]
[TD="align: center"]1.77[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2.32[/TD]
[TD="align: center"]2.67[/TD]
[TD="align: center"]2.9[/TD]
[TD="align: center"]3.47[/TD]
[TD="align: center"]4.41[/TD]
[TD="align: center"]5.16[/TD]
[TD="align: center"]6.22[/TD]
[TD="align: center"]7.06[/TD]
[TD="align: center"]8.12[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]48-HR[/TD]
[TD="align: center"]1.4[/TD]
[TD="align: center"]1.64[/TD]
[TD="align: center"]1.82[/TD]
[TD="align: center"]2.11[/TD]
[TD="align: center"]2.43[/TD]
[TD="align: center"]2.64[/TD]
[TD="align: center"]3.13[/TD]
[TD="align: center"]3.93[/TD]
[TD="align: center"]4.67[/TD]
[TD="align: center"]5.75[/TD]
[TD="align: center"]6.52[/TD]
[TD="align: center"]7.33[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]24-HR[/TD]
[TD="align: center"]1.31[/TD]
[TD="align: center"]1.52[/TD]
[TD="align: center"]1.67[/TD]
[TD="align: center"]1.93[/TD]
[TD="align: center"]2.19[/TD]
[TD="align: center"]2.38[/TD]
[TD="align: center"]2.91[/TD]
[TD="align: center"]3.64[/TD]
[TD="align: center"]4.27[/TD]
[TD="align: center"]5.15[/TD]
[TD="align: center"]5.87[/TD]
[TD="align: center"]6.61[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]18-HR[/TD]
[TD="align: center"]1.23[/TD]
[TD="align: center"]1.43[/TD]
[TD="align: center"]1.57[/TD]
[TD="align: center"]1.81[/TD]
[TD="align: center"]2.06[/TD]
[TD="align: center"]2.24[/TD]
[TD="align: center"]2.74[/TD]
[TD="align: center"]3.42[/TD]
[TD="align: center"]4.01[/TD]
[TD="align: center"]4.84[/TD]
[TD="align: center"]5.52[/TD]
[TD="align: center"]6.21[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]12-HR[/TD]
[TD="align: center"]1.14[/TD]
[TD="align: center"]1.32[/TD]
[TD="align: center"]1.45[/TD]
[TD="align: center"]1.68[/TD]
[TD="align: center"]1.9[/TD]
[TD="align: center"]2.07[/TD]
[TD="align: center"]2.53[/TD]
[TD="align: center"]3.17[/TD]
[TD="align: center"]3.71[/TD]
[TD="align: center"]4.48[/TD]
[TD="align: center"]5.11[/TD]
[TD="align: center"]5.75[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]6-HR[/TD]
[TD="align: center"]0.98[/TD]
[TD="align: center"]1.15[/TD]
[TD="align: center"]1.25[/TD]
[TD="align: center"]1.45[/TD]
[TD="align: center"]1.65[/TD]
[TD="align: center"]1.79[/TD]
[TD="align: center"]2.18[/TD]
[TD="align: center"]2.73[/TD]
[TD="align: center"]3.2[/TD]
[TD="align: center"]3.86[/TD]
[TD="align: center"]4.4[/TD]
[TD="align: center"]4.96[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]3-HR[/TD]
[TD="align: center"]0.84[/TD]
[TD="align: center"]0.97[/TD]
[TD="align: center"]1.06[/TD]
[TD="align: center"]1.23[/TD]
[TD="align: center"]1.4[/TD]
[TD="align: center"]1.52[/TD]
[TD="align: center"]1.86[/TD]
[TD="align: center"]2.33[/TD]
[TD="align: center"]2.73[/TD]
[TD="align: center"]3.3[/TD]
[TD="align: center"]3.76[/TD]
[TD="align: center"]4.23[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]2-HR[/TD]
[TD="align: center"]0.76[/TD]
[TD="align: center"]0.88[/TD]
[TD="align: center"]0.97[/TD]
[TD="align: center"]1.12[/TD]
[TD="align: center"]1.27[/TD]
[TD="align: center"]1.38[/TD]
[TD="align: center"]1.69[/TD]
[TD="align: center"]2.11[/TD]
[TD="align: center"]2.48[/TD]
[TD="align: center"]2.99[/TD]
[TD="align: center"]3.4[/TD]
[TD="align: center"]3.83[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1-HR[/TD]
[TD="align: center"]0.62[/TD]
[TD="align: center"]0.72[/TD]
[TD="align: center"]0.78[/TD]
[TD="align: center"]0.91[/TD]
[TD="align: center"]1.03[/TD]
[TD="align: center"]1.12[/TD]
[TD="align: center"]1.37[/TD]
[TD="align: center"]1.71[/TD]
[TD="align: center"]2.01[/TD]
[TD="align: center"]2.42[/TD]
[TD="align: center"]2.76[/TD]
[TD="align: center"]3.11[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]30-MIN[/TD]
[TD="align: center"]0.48[/TD]
[TD="align: center"]0.56[/TD]
[TD="align: center"]0.62[/TD]
[TD="align: center"]0.71[/TD]
[TD="align: center"]0.81[/TD]
[TD="align: center"]0.88[/TD]
[TD="align: center"]1.08[/TD]
[TD="align: center"]1.35[/TD]
[TD="align: center"]1.58[/TD]
[TD="align: center"]1.91[/TD]
[TD="align: center"]2.17[/TD]
[TD="align: center"]2.45[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]15-MIN[/TD]
[TD="align: center"]0.35[/TD]
[TD="align: center"]0.41[/TD]
[TD="align: center"]0.45[/TD]
[TD="align: center"]0.52[/TD]
[TD="align: center"]0.59[/TD]
[TD="align: center"]0.64[/TD]
[TD="align: center"]0.79[/TD]
[TD="align: center"]0.98[/TD]
[TD="align: center"]1.15[/TD]
[TD="align: center"]1.39[/TD]
[TD="align: center"]1.58[/TD]
[TD="align: center"]1.78[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]10-MIN[/TD]
[TD="align: center"]0.28[/TD]
[TD="align: center"]0.32[/TD]
[TD="align: center"]0.35[/TD]
[TD="align: center"]0.41[/TD]
[TD="align: center"]0.46[/TD]
[TD="align: center"]0.5[/TD]
[TD="align: center"]0.61[/TD]
[TD="align: center"]0.76[/TD]
[TD="align: center"]0.9[/TD]
[TD="align: center"]1.08[/TD]
[TD="align: center"]1.23[/TD]
[TD="align: center"]1.39[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]5-MIN[/TD]
[TD="align: center"]0.16[/TD]
[TD="align: center"]0.19[/TD]
[TD="align: center"]0.2[/TD]
[TD="align: center"]0.23[/TD]
[TD="align: center"]0.27[/TD]
[TD="align: center"]0.29[/TD]
[TD="align: center"]0.35[/TD]
[TD="align: center"]0.44[/TD]
[TD="align: center"]0.51[/TD]
[TD="align: center"]0.62[/TD]
[TD="align: center"]0.7[/TD]
[TD="align: center"]0.79[/TD]
[/TR]
</tbody>[/TABLE]

 

[Matty]

Hi,

It sounds like an INDEX(MATCH(),MATCH()) formula is what you need.

What is your row lookup value? The column lookup value seems to be "4-MO".

Matty

 

[Jonmo1]

Vlookup returns a value, not a range (or a named range)
So if vlookup is returning Sec_5, the result of the vlookup is just a text string "Sec_5"
Index won't use it as a range.

You need the INDIRECT function

=INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH(B10,Duration,0),5)

 

[MarcelBeug]

Probably the error is with the result of the VLOOKUP which is a string "Sec_5" and not an actual range.

A solution would be to create a table (e.g. NameDefs) with all defined names with their ranges (you can use F3 to create this list, but you'd better remove the "=" from the ranges) and add an INDIRECT function and another VLOOKUP to retrieve the range of "Sec_5", so your code becomes (something like):

=INDEX(INDIRECT(VLOOKUP(VLOOKUP($D$2,Section,2,FALSE),NameDefs,2,FALSE)),MATCH(B10,Duration,0),5)

Update: this is similar to JonMo's solution, however as far as I know it's not possible to use a defined name as argument with INDIRECT. (?)

 

[bigmyk2k]

Probably the error is with the result of the VLOOKUP which is a string "Sec_5" and not an actual range.

A solution would be to create a table (e.g. NameDefs) with all defined names with their ranges (you can use F3 to create this list, but you'd better remove the "=" from the ranges) and add an INDIRECT function and another VLOOKUP to retrieve the range of "Sec_5", so your code becomes (something like):

=INDEX(INDIRECT(VLOOKUP(VLOOKUP($D$2,Section,2,FALSE),NameDefs,2,FALSE)),MATCH(B10,Duration,0),5)

Update: this is similar to JonMo's solution, however as far as I know it's not possible to use a defined name as argument with INDIRECT. (?)

The Indirect actually did the job on the primary question- no NameDefs needed, so thank you for the help!!
Though I may ask for help with the next step: I need to make the column number a match too.

Given the same table, if I have a 24-HR value of 1.72, that would be between the 4-MO and 6-MO columns. If I wanted to know just that (perhaps two different functions telling me those values) I think that would be another match in the Column argument, but it is a little more complicated than I can wrap my head around today. Any thoughts?

 

[Jonmo1]

however as far as I know it's not possible to use a defined name as argument with INDIRECT. (?)

We're not using a defined name as the argument in indirect.
The argument in indirect is a TEXT string, remember...Returned by Vlookup.
Indirect uses the TEXT string to create the range.

 

[Jonmo1]

I need to make the column number a match too.

Before we move on to that, just confirm that the provided solution does actually return the desired result based on the 5 hard-coded as the column#..
If so,

What exactly are you trying to do now?
Find 1.72 in the row that was found by MATCH(B10,Duration,0) ?
And verify the numbers are sorted ascending left to right per row ?

 

[MarcelBeug]

We're not using a defined name as the argument in indirect.
The argument in indirect is a TEXT string, remember...Returned by Vlookup.
Indirect uses the TEXT string to create the range.

Ah, I see: the argument is a defined name in double quotes. I tried without double quotes.

But let's not hijack this topic, but switch back to the follow up question:

Though I may ask for help with the next step: I need to make the column number a match too.

Given the same table, if I have a 24-HR value of 1.72, that would be between the 4-MO and 6-MO columns. If I wanted to know just that (perhaps two different functions telling me those values) I think that would be another match in the Column argument, but it is a little more complicated than I can wrap my head around today. Any thoughts?

 

[bigmyk2k]

Before we move on to that, just confirm that the provided solution does actually return the desired result based on the 5 hard-coded as the column#..

That is correct, it works exactly right given hard coded manipulation of the Column reference.

If so,

What exactly are you trying to do now?
Find 1.72 in the row that was found by MATCH(B10,Duration,0) ?
And verify the numbers are sorted ascending left to right per row ?

I'm not trying to verify their order (that is already certain).
I'm trying to find the two values in the table in the given row which bound a value. So, 1.67 < 1.72 < 1.93
If I am using 1.72 as a lookup value, how can I get Match to return the value 1.67 (or, more specifically, the column number "5")?

 

[Jonmo1]

I'm not trying to verify their order (that is already certain).

Sorry, that's not what I meant. I was asking if the numbers were already sorted...

Anyway..

That's actually even simpler..
No need for the INDIRECT(VLOOKUP

Try
=LOOKUP(1.72,OFFSET(INDEX(Duration,MATCH(B10,Duration,0)),0,1,1,12))