#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>
 

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
A bit late, but...if there aren't too many named section ranges...

Using this list in I1:J6
Code:
Ref      SecRef
Alpha     Sec_1
Bravo     Sec_2
Charlie   Sec_3
Delta     Sec_4
Echo      Sec_5

Note: the SecRef is just for illustration purposes. The real driver is the Ref column, which, when matched in the CHOOSE function, selects the correct section.

This regular, non-volatile formula seems to work:
Code:
=INDEX(CHOOSE(MATCH($D$2,$I$2:$I$6,0),Sec_1,Sec_2,Sec_3,Sec_4,Sec_5),MATCH(B10,Duration,0),5)

Is that something you can work with?
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

bigmyk2k

Board Regular
Joined
Feb 9, 2012
Messages
101
Sorry, that's not what I meant.
No worries, I think I misunderstood.

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

That doesn't seem to be working, as the value "1.72" is not present in the row referenced.

To be a little more clear, I am looking to add a match to the original function, in order to also look up the Column Reference based on another value... maybe I should start another thread, since I am deviating from the original question?

Anyway, I am looking to fill out the following function:

=INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH($B$10,Duration,0),MATCH($B$9,)

OR (Evaluated to the point I'm asking about):

=INDEX(INDIRECT(Sec_5),5,MATCH($B$9,...)

Where- B9 = 1.72
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Works for me, formua in D10
B10 = 24-HR
C10 = 1.72

Excel Workbook
BCDEFGHIJKLMNOPQRS
1SectionDuration2-MO3-MO4-MO6-MO9-MO1-YR2-YR5-YR10-YR25-YR50-YR100-YR
2510-DAY2.22.643.053.584.124.485.26.227.228.619.6610.88
355-DAY1.762.112.392.773.183.464.054.945.726.927.989.18
4572-HR1.511.7722.322.672.93.474.415.166.227.068.12
5548-HR1.41.641.822.112.432.643.133.934.675.756.527.33
6524-HR1.311.521.671.932.192.382.913.644.275.155.876.61
7518-HR1.231.431.571.812.062.242.743.424.014.845.526.21
8512-HR1.141.321.451.681.92.072.533.173.714.485.115.75
956-HR0.981.151.251.451.651.792.182.733.23.864.44.96
1024-HR1.721.6753-HR0.840.971.061.231.41.521.862.332.733.33.764.23
1152-HR0.760.880.971.121.271.381.692.112.482.993.43.83
1251-HR0.620.720.780.911.031.121.371.712.012.422.763.11
13530-MIN0.480.560.620.710.810.881.081.351.581.912.172.45
14515-MIN0.350.410.450.520.590.640.790.981.151.391.581.78
15510-MIN0.280.320.350.410.460.50.610.760.91.081.231.39
1655-MIN0.160.190.20.230.270.290.350.440.510.620.70.79
Sheet1
#VALUE!
</td></tr></table></td></tr></table>
 

bigmyk2k

Board Regular
Joined
Feb 9, 2012
Messages
101
That's close, but I think the confusion may be the named ranges.

The data you are calling "Duration" I am calling "Sec_5", and looking up with the indirect VLookup because there are 9 sections in my current data.

What my sheet refers to with the named range "Duration" is simply the drop-down list:
Duration
10-DAY
5-DAY
72-HR
48-HR
24-HR
18-HR
12-HR
6-HR
3-HR
2-HR
1-HR
30-MIN

<tbody>
</tbody>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

The data you are calling "Duration" I am calling "Sec_5", and looking up with the indirect VLookup because there are 9 sections in my current data.
No, your original formula used the range named 'Duration' to find the row #.

My Function is: =INDEX(VLOOKUP($D$2,Section,2,FALSE),MATCH(B10,Duration,0),5)

Then the relevant numbers were directly to the right of the column named Duration...

is that not how it's set up?
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
I think I get it now..

You have several Sections, all of the same dimensions.
But the Duration range you used to find the row# was not the one in Sec_5
You just used any one of them, because they're all the same...


Try this

=LOOKUP(C10,OFFSET(INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH(B10,Duration,0),3),,,1,12))

Again, C10 = 1.72, B10 = 24-HR
 
Last edited:

bigmyk2k

Board Regular
Joined
Feb 9, 2012
Messages
101

ADVERTISEMENT

I think I get it now..

You have several Sections, all of the same dimensions.
But the Duration range you used to find the row# was not the one in Sec_5
You just used any one of them, because they're all the same...


Try this

=LOOKUP(C10,OFFSET(INDEX(INDIRECT(VLOOKUP($D$2,Section,2,FALSE)),MATCH(B10,Duration,0),3),,,1,12))

Again, C10 = 1.72, B10 = 24-HR

Hey Jon, Thanks for your help. That still doesn't work, but I think it is because I am asking a simplified version of the bigger question.
We have answered the original question from this thread, so I'm going to start a new thread and try to be a little more clear.
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
No problem.
Make sure to include a link to this thread in your new thread.
So that responders can see what has already been tried, so to avoid duplicating work.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,824
Messages
5,544,541
Members
410,619
Latest member
gregor222
Top