#VALUE! Error on Index Function

bigmyk2k · Jun 3, 2015

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:

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

Ron Coderre · Jun 3, 2015

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?

bigmyk2k · Jun 3, 2015

Jonmo1 said:
Sorry, that's not what I meant.

No worries, I think I misunderstood.

Jonmo1 said:
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 · Jun 3, 2015

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

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 · Jun 3, 2015

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

Jonmo1 · Jun 3, 2015

bigmyk2k said:
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 #.

bigmyk2k said:
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 · Jun 3, 2015

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 · Jun 4, 2015

Jonmo1 said:
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 · Jun 4, 2015

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 · Jun 4, 2015

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

#VALUE! Error on Index Function

bigmyk2k

Board Regular

Ron Coderre

MrExcel MVP

Excel Facts

bigmyk2k

Board Regular

Jonmo1

MrExcel MVP

bigmyk2k

Board Regular

Jonmo1

MrExcel MVP

Jonmo1

MrExcel MVP

bigmyk2k

Board Regular

Jonmo1

MrExcel MVP

bigmyk2k

Board Regular

Similar threads

Forum statistics

Share this page

#VALUE! Error on Index Function

Board Regular

MrExcel MVP

Excel Facts

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock