Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: #VALUE! Error on Index Function

  1. #1
    Board Regular
    Join Date
    Feb 2012
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default #VALUE! Error on Index Function

    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

  2. #2
    Board Regular
    Join Date
    Feb 2007
    Location
    England
    Posts
    3,692
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    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

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    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)
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    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 by MarcelBeug; Jun 3rd, 2015 at 03:26 PM.

  5. #5
    Board Regular
    Join Date
    Feb 2012
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    Quote Originally Posted by MarcelBeug View Post
    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?

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    Quote Originally Posted by MarcelBeug View Post
    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.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    Quote Originally Posted by bigmyk2k View Post
    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 ?
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  8. #8
    Board Regular MarcelBeug's Avatar
    Join Date
    Apr 2014
    Posts
    1,811
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    Quote Originally Posted by Jonmo1 View Post
    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:

    Quote Originally Posted by bigmyk2k View Post
    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?

  9. #9
    Board Regular
    Join Date
    Feb 2012
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    Quote Originally Posted by Jonmo1 View Post
    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.
    Quote Originally Posted by Jonmo1 View Post
    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 by bigmyk2k; Jun 3rd, 2015 at 04:00 PM. Reason: Clarification

  10. #10
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    44,054
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    0 Thread(s)

    Default Re: #VALUE! Error on Index Function

    Quote Originally Posted by bigmyk2k View Post
    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))
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •