Results 1 to 10 of 10

Thread: Refrencing a cell in a Index Formula

  1. #1
    New Member
    Join Date
    Jan 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Refrencing a cell in a Index Formula

    What I do is pull data from multiple sources on the internet sometimes it can be 500 rows sometimes 10000. I then have a sheet that uses a formula like =INDEX(B1:B10000,MATCH(R2,A1:A10000,0). I can set a cell up in say S1 That tells me there is 600 rows of data. is there a way to reference the 600 in Cell S1 to place the 10000 in the formula.

    Thanks
    Adam

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    How about
    =INDEX(INDIRECT("B1:B"&S1),MATCH(R2,INDIRECT("A1:A"&S1),0))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular arthurbr's Avatar
    Join Date
    Dec 2006
    Location
    Belgium
    Posts
    1,890
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    Perhaps=INDEX(B1:INDEX(B1:B100000,S1),MATCH(R2,INDEX(A1:INDEX(A1:A100000,S1))),0)
    Last edited by arthurbr; Sep 15th, 2019 at 10:34 AM.
    Please,always use a relevant keyword thread title that describes what you are trying to do
    Using XL2003 - 2010

  4. #4
    New Member
    Join Date
    Jan 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    Quote Originally Posted by Fluff View Post
    How about
    =INDEX(INDIRECT("B1:B"&S1),MATCH(R2,INDIRECT("A1:A"&S1),0))

    Thank you much I tried this and it looks like it works now lets see if it speeds up my calculations.


    Thanks again

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    Try the formula posted by arthurbr as that is non-volatile & should be better.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    New Member
    Join Date
    Jan 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    Quote Originally Posted by Fluff View Post
    Try the formula posted by arthurbr as that is non-volatile & should be better.
    I tried but get an error message that i entered too few arguments?

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    Try
    =INDEX(B1:INDEX(B1:B100000,S1),MATCH(R2,INDEX(A1:INDEX(A1:A100000,S1),0),0))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    New Member
    Join Date
    Jan 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    Still get the same too few arguments?

  9. #9
    New Member
    Join Date
    Jan 2016
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    Quote Originally Posted by Fluff View Post
    Try
    =INDEX(B1:INDEX(B1:B100000,S1),MATCH(R2,INDEX(A1:INDEX(A1:A100000,S1),0),0))
    Now it is working. Thanks for the help not sure why i was getting error before.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,190
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Refrencing a cell in a Index Formula

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •