Results 1 to 3 of 3

Thread: INDEX/MATCH with Dynamic Ranges
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2008
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default INDEX/MATCH with Dynamic Ranges

    Using Excel2007
    Workbook contains 2 sheets,Combined and Reports
    On Combined, columnB=Invoice No, C=Account No, E=Invoice Date
    On Reports, columnAS=Invoice No, AT=Account No, AU=Invoice Date, AV=Currency
    (the other columns on each sheet are not relevant at the moment)
    Created Named Ranges as follows:
    SAcc= =OFFSET(Reports!$AT$5,0,0,COUNTA(Reports!$AT:$AT),1)
    SDte= =OFFSET(Reports!$AU$5,0,0,COUNT(Reports!$AU:$AU),1)
    SRef= =OFFSET(Reprts!$AS$5,0,0,COUNTA(Reports!$AS:$AS),1)
    SFnd= =OFFSET(Reports!$AS$5,0,0,COUNTA(Reports!$AS:$AS),3)
    If I use Range("D5").FormulaArray="=INDEX(Reports!AV$5:AV$1932,MATCH(B5&C5&E5,SRef&SAcc&SDte,0))" I get the result I want, but if I change the formula to "=INDEX(Reports!AV$5:AV$1932,MATCH(B5&C5&E5,SFnd,0))" I get the A Value is no available to the formula or function error.
    Have tried changing SFnd to only include 2 columns and using SFnd&SDte in the formula but I still can't get the right result.
    This becomes even more important in a formula I need later where I am coming up against the 255 character limit.
    Can you help me please

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: INDEX/MATCH with Dynamic Ranges

    Your first formula matches with a concatenated array. Your second formula is trying to match with a range of 3 columns, which is not the same thing.
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Aug 2008
    Posts
    60
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDEX/MATCH with Dynamic Ranges

    Sorry for the delay in replying. I see what you mean, it's nice that when I'm having a "dumb" day you are there to point me in the right direction
    Thanks again

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
  •