Index Match
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Index Match
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2017
    Location
    Minnesota,USA
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Index Match

    I feel like my brain just isn't working correctly, but here is what I am trying to do.

    I have 2 tables, I need to add a column to the first table, and then do a lookup for the "ITEM ID" from the second table and return the "VERIFY" number to the NEW column in the first table.

    I am pretty sure this is a decently simple Index match formula, but for the life of me I am not getting it to work...

    First Table: (1 Column)
    Folder ID
    04.315.201 6/11/2019 PO 1
    04.315.201 6/11/2019 PO 2
    04.315.202 6/11/2019 PO 1
    04.315.202 6/11/2019 PO 2
    04.315.311 6/11/2019 PO 1
    04.315.311 6/11/2019 PO 2

    Second Table: (2 Columns)
    ITEM ID VERIFY
    04.315.201 1
    04.315.202 2
    04.315.311 3


    Thanks for any help!
    -Ben

  2. #2
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,830
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Index Match Help

    what is your current formula
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  3. #3
    New Member
    Join Date
    Jan 2017
    Location
    Minnesota,USA
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match Help

    Quote Originally Posted by mole999 View Post
    what is your current formula
    What I am currently working with is the following type. But obviously in my sheet my tables are named (Second Tables = Tbl_Item)
    =INDEX(tbl_Item,MATCH(tbl_Item[Item ID],[Folder ID],0),2)

  4. #4
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match Help

    Maybe...


    A
    B
    C
    D
    E
    F
    1
    Folder ID
    Verify
    ITEM ID
    VERIFY
    2
    04.315.201 6/11/2019 PO 1
    1
    04.315.201
    1
    3
    04.315.201 6/11/2019 PO 2
    1
    04.315.202
    2
    4
    04.315.202 6/11/2019 PO 1
    2
    04.315.311
    3
    5
    04.315.202 6/11/2019 PO 2
    2
    6
    04.315.311 6/11/2019 PO 1
    3
    7
    04.315.311 6/11/2019 PO 2
    3
    8


    Formula in B2
    =INDEX(tbl_Item[VERIFY],MATCH(LEFT([@[Folder ID]],10),tbl_Item[ITEM ID],0))

    Hope this helps

    M.

  5. #5
    New Member
    Join Date
    Jan 2017
    Location
    Minnesota,USA
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match Help

    Quote Originally Posted by Marcelo Branco View Post
    Maybe...


    A
    B
    C
    D
    E
    F
    1
    Folder ID
    Verify
    ITEM ID
    VERIFY
    2
    04.315.201 6/11/2019 PO 1
    1
    04.315.201
    1
    3
    04.315.201 6/11/2019 PO 2
    1
    04.315.202
    2
    4
    04.315.202 6/11/2019 PO 1
    2
    04.315.311
    3
    5
    04.315.202 6/11/2019 PO 2
    2
    6
    04.315.311 6/11/2019 PO 1
    3
    7
    04.315.311 6/11/2019 PO 2
    3
    8


    Formula in B2
    =INDEX(tbl_Item[VERIFY],MATCH(LEFT([@[Folder ID]],10),tbl_Item[ITEM ID],0))

    Hope this helps

    M.
    I was thinking that originally, but the issue is that the LEFT portion before the date changes since they are based on a totally random item number, that first number string could vary from like 7-14 characters i believe (Another Example: AC-3010 4/26/2019 PO 1 & HCS-010-25-22L 6/28/2019 PO 1)

  6. #6
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,830
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Index Match Help

    is the first string part always without spaces?
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  7. #7
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Index Match Help

    If there are no spaces in the number and the first space only occurs between this number and date then @Marcelo Branco's formula can be changed to:

    Code:
    =INDEX(tbl_Item[VERIFY],MATCH(LEFT([@[Folder ID]],SEARCH(" ",[@[Folder ID]])-1),tbl_Item[ITEM ID],0))
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  8. #8
    New Member
    Join Date
    Jan 2017
    Location
    Minnesota,USA
    Posts
    29
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match Help

    Quote Originally Posted by mole999 View Post
    is the first string part always without spaces?
    If you are referring to the Item ID, yes. The Item ID does not contain any spaces.

    Also if you really wanted to eliminate the spaces you could utilize a TRIM formula(If it was needed).

  9. #9
    Moderator mole999's Avatar
    Join Date
    Oct 2004
    Location
    UK
    Posts
    9,830
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Index Match Help

    you need to search for the space, as Aryatect said above
    • Yes I know there are better ways to do it. I just wish I knew them. - 97, 2003, 2007, 2010, 2013, 2016 & 2019
    • I wear my ignorance openly, excel is not my chosen career, its a means to an ends
    • Posting Guidelines Want to post well laid out questions and answers Translate Excel Versions
      Code:
      [CODE ]Put Your Code[/ CODE]
    • Settings > General Settings (on the left) scroll to the bottom, > Miscellaneous Options > Use ENHANCED
    • X-Posting Guidelines Rule 13 > CHART STUFF

  10. #10
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,239
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match Help

    Try

    B2
    =INDEX(tbl_Item[VERIFY],MATCH(1,INDEX(--ISNUMBER(SEARCH(tbl_Item[ITEM ID],[@[Folder ID]])),),0))

    M.

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
  •