#VALUE! Error on Index Function

bigmyk2k

Board Regular
Joined
Feb 9, 2012
Messages
101
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:
SectionDuration2-MO3-MO4-MO6-MO9-MO1-YR2-YR5-YR10-YR25-YR50-YR100-YR
510-DAY2.22.643.053.584.124.485.26.227.228.619.6610.88
55-DAY1.762.112.392.773.183.464.054.945.726.927.989.18
572-HR1.511.7722.322.672.93.474.415.166.227.068.12
548-HR1.41.641.822.112.432.643.133.934.675.756.527.33
524-HR1.311.521.671.932.192.382.913.644.275.155.876.61
518-HR1.231.431.571.812.062.242.743.424.014.845.526.21
512-HR1.141.321.451.681.92.072.533.173.714.485.115.75
56-HR0.981.151.251.451.651.792.182.733.23.864.44.96
53-HR0.840.971.061.231.41.521.862.332.733.33.764.23
52-HR0.760.880.971.121.271.381.692.112.482.993.43.83
51-HR0.620.720.780.911.031.121.371.712.012.422.763.11
530-MIN0.480.560.620.710.810.881.081.351.581.912.172.45
515-MIN0.350.410.450.520.590.640.790.981.151.391.581.78
510-MIN0.280.320.350.410.460.50.610.760.91.081.231.39
55-MIN0.160.190.20.230.270.290.350.440.510.620.70.79

<colgroup><col><col><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,710
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
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):

Code:
=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. (?)
 
Last edited:

bigmyk2k

Board Regular
Joined
Feb 9, 2012
Messages
101

ADVERTISEMENT

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):

Code:
=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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
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

Board Regular
Joined
Feb 9, 2012
Messages
101
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")?
 
Last edited:

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,114,068
Messages
5,545,800
Members
410,708
Latest member
SanTrapGamer
Top