Consulting

Results 1 to 6 of 6

Thread: Using Index Match with an unknown range

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    Using Index Match with an unknown range

    I have a working formula in Column D of the attached WorkSheet.
    =INDEX(Posted_Data!$BK$3:$BK$11,MATCH(B3,Posted_Data!$AT$3:$AT$11,0))
    I am using this Index Match function with a hard coded range of A Sheet Named Posted_Data ,
    So Posted_Data!$BK$3:$BK$11, However, I would like to make this search range Dynamic based on the value that is listed in My Formula Colum, Row 2, so that the search range changes depending on the current value of the Active Column Row 2 value
    Right now, my formula is in Column D, so when I move to Column E, the search range happens to be in !$BL$3:$BL$11,
    but that will NOT always be the case, so I need to first search in sheet Posted_Data Range Range BK2:BV2 for the value in ActiveCell Row2
    ( Finding the Appropriate month )
    The formulas are in Sheets(“Report”)
    How do I modify my formulas to accommodate this on the Attached WorkSheet ?
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Cell D3, normally-entered formula:
    =INDEX(Posted_Data!$3:$17,MATCH($B3,Posted_Data!$AT$3:$AT$17,0),MATCH(D$2,Posted_Data!$2:$2,0))
    copied down and across.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    p45cal

    Thanks. It works as expected.
    It was that second Match function that tripped me.

  4. #4
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    p45cal

    Didn't realize that my follow up question was not posted. How did you arrive at
    MATCH(D$2,Posted_Data!$2:$2
    I tried to replicate it using the actual range Posted_Data!$BK$2:$BV$2,
    but it gives me an error.

  5. #5
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    =INDEX(Posted_Data!$BK$3:$BV$17,MATCH($B3,Posted_Data!$AT$3:$AT$17,0),MATCH(D$2,Posted_Data!$BK$2:$BV$2,0))
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #6
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    p45cal:

    Again; THANKS !
    I did NOT explain my problem. I did not understant the Cell logic notation of
    [ Posted_Data!$2:$2,0) ] OR [ Posted_Data!$3:$17 ]
    as it relates to the appropriate Worksheet.

Posting Permissions

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