#VALUE! Error on Index Function

bigmyk2k

Board Regular
Joined
Feb 9, 2012
Messages
104
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>
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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
 
Upvote 0
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)
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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 ?
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top