Thanks Thanks:  0
Likes Likes:  0
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: Accomodate Range in INDEX, INDIRECT

  1. #1
    New Member
    Join Date
    Nov 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Accomodate Range in INDEX, INDIRECT

    My formula reads like the following


    =INDEX(INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))


    where 'tt' is a table name


    B1 to B20 contains data that is both as single numbers as well as ranges

    -
    -
    0
    1
    2-3
    4
    5-6
    7
    8
    9-10
    11
    12-13
    14
    15
    16-17
    18
    19-20
    21
    22
    23-24
    25


    The formula works perfectly fine when the number in B1:B20 is a single number, but as soon as there's a range like in B5, B7 and so on, it returns a #NA


    How can I change the above formula to accomodate range in cells?

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    Does this...

    =INDEX(INDIRECT("'"&VLOOKUP(A5,tt,2,0)&"'!A1:A20"),MATCH(D1,INDIRECT("'"&VLOOKUP(A5,tt,2,0)&"'!B1:B20"),0))

    help?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    Nov 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    Thanks for the answer Aladin. The 'tt' holds the name of excel sheets, so I do not want any change there.

    I think I need to change something in the second portion of the formula to accomodate cells containing range MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    Quote Originally Posted by vivamar View Post
    Thanks for the answer Aladin. The 'tt' holds the name of excel sheets, so I do not want any change there.

    I think I need to change something in the second portion of the formula to accomodate cells containing range MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))
    I guess tt is sorted; and it is possibly numeric.

    Back to the MATCH bit...

    Can D1 be 2 as well as 3?
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    Nov 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    A1 to A20 contains a sequential number from 1 to 20

    B1 to B20 contains this

    -
    -
    0
    1
    2-3
    4
    5-6
    7
    8
    9-10
    11
    12-13
    14
    15
    16-17
    18
    19-20
    21
    22
    23-24
    25

    tt contains this

    5.0 5.2 to 5.7
    5.8 5.8 to 7.1

    As you can see, I am not really bothered by the Named table as it just looks up the sheet names.

    D1 can contain only a single number at a time from 0 to 25. I have to take this number and lookup B1 to B20 and pick corresponding A1 to A20 value. Since the column B contains ranges like 23-24 or 9-10, the formula is failing
    Last edited by vivamar; Nov 24th, 2013 at 03:08 AM.

  6. #6
    New Member
    Join Date
    Nov 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    Anybody? Just need to know what do I put here instead of the Bold text to accomodate ranges

    MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    Quote Originally Posted by vivamar View Post
    Anybody? Just need to know what do I put here instead of the Bold text to accomodate ranges

    MATCH(D1,INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20"),0))
    Does D1 contains 2-3 or either 2 or 3?
    Assuming too much and qualifying too much are two faces of the same problem.

  8. #8
    New Member
    Join Date
    Nov 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    either 2 or 3

  9. #9
    New Member
    Join Date
    Nov 2013
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    Quote Originally Posted by Aladin Akyurek View Post
    Does D1 contains 2-3 or either 2 or 3?
    D1 can contain either 2 or 3

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,915
    Post Thanks / Like
    Mentioned
    43 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Accomodate Range in INDEX, INDIRECT

    Quote Originally Posted by vivamar View Post
    either 2 or 3
    The table you put up runs from B1 to B21, not to B20. Adjust A1:A20 and B1:B20 to suit if necessary.

    Note that I tested with a single range...
    Code:
    =INDEX(
      INDIRECT("'"&LOOKUP(A5,tt)&"'!A1:A20"),
       LOOKUP(9.99999999999999E+307,
        SEARCH("-"D1&"-","-"&INDIRECT("'"&LOOKUP(A5,tt)&"'!B1:B20")&"-"),
        ROW(B1:B20)-ROW(B1)+1))
    
    Does this deliver the good?
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

Tags for this Thread

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
  •