Results 1 to 7 of 7

Thread: Index Match is pulling from different row than intended
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index Match is pulling from different row than intended

    I am trying to index a date from another worksheet with the formula

    =TEXT(INDEX('JAN-APR INV'!$A:$I,MATCH(YTD!A247,YTD!A:A,0),6),"mm/dd/yy")

    I want the formula to pull date based on the ID associated which is column A. The date is in worksheet "Jan-Apr Inv" row 6. It will pull the date from A247, but doesnt line up with ID on YTD worksheet.

    I don't know if index match is the right formula or if another formula is needed in this situation.

  2. #2
    Board Regular
    Join Date
    Aug 2013
    Posts
    67
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match is pulling from different row than intended

    Hi Ezybrrzy,

    The INDEX has following parameters:
    - Lookup_Range - Array
    - Row_Num
    - Column_Num

    You are writing that the date is in row 6, however, in your formula 6 is referenced as column_number, while MATCH formula is for row_number. Can you please check if this might be the problem, if not, can you provide a bit more information how the data looks like?

    Br
    pella88

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

    Default Re: Index Match is pulling from different row than intended

    Hi & welcome to MrExcel.
    Should this
    MATCH(YTD!A247,YTD!A:A,0),6)
    be
    MATCH(YTD!A247,'JAN-APR INV'!A:A,0),6)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match is pulling from different row than intended

    Sorry, yes it should be "column 6" not "row 6"

    The MATCH(YTD!A247,'JAN-APR INV'!A:A,0),6) should be MATCH(YTD!A247,'JAN-APR INV'!B:B,0),6), as the ID on that worksheet is in column B.

    The formula works in a sense it pulls the information in row 247 from worksheet "Jan-APR INV" into row 247 on the "YTD" worksheet. It disregards matching ID # from the "YTD" worksheet that i want it to pull from. The date in row 247 in the "JAN-APR INV" worksheet would be 1/19/19, but the actual date i want is 4/18/19 from row 2417.

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

    Default Re: Index Match is pulling from different row than intended

    Does the ID number exist multiple times on the Jan-Apr sheet, or only once?
    - 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
    Sep 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match is pulling from different row than intended

    It exists multiple times

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

    Default Re: Index Match is pulling from different row than intended

    In that case your formula will only ever pick-up the first occurrence.
    I would suggest you post some sample data along with an accurate description of what you are trying do.
    - 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
  •