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

 

[Ron Coderre]

A bit late, but...if there aren't too many named section ranges...

Using this list in I1:J6

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:

=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?

 

[bigmyk2k]

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]

Works for me, formua in D10
B10 = 24-HR
C10 = 1.72

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1					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
2					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
3					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
4					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					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
6					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
7					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
8					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
9					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
10	24-HR	1.72	1.67		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
11					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
12					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
13					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
14					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
15					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
16					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
Sheet1

#VALUE!

</td></tr></table></td></tr></table>

 

[bigmyk2k]

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:
[TABLE="width: 54"]
<tbody>[TR]
[TD]Duration[/TD]
[/TR]
[TR]
[TD]10-DAY[/TD]
[/TR]
[TR]
[TD]5-DAY[/TD]
[/TR]
[TR]
[TD]72-HR[/TD]
[/TR]
[TR]
[TD]48-HR[/TD]
[/TR]
[TR]
[TD]24-HR[/TD]
[/TR]
[TR]
[TD]18-HR[/TD]
[/TR]
[TR]
[TD]12-HR[/TD]
[/TR]
[TR]
[TD]6-HR[/TD]
[/TR]
[TR]
[TD]3-HR[/TD]
[/TR]
[TR]
[TD]2-HR[/TD]
[/TR]
[TR]
[TD]1-HR[/TD]
[/TR]
[TR]
[TD]30-MIN[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

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]

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

 

[bigmyk2k]

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]

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.

 

[bigmyk2k]

Ok, see below for continuation, and clarification.
http://www.mrexcel.com/forum/excel-questions/859214-n-error-complicated-index-match.html