+ Reply to Thread
Results 1 to 11 of 11

Index and Match Help

  1. #1
    Registered User
    Join Date
    03-09-2018
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2013
    Posts
    35

    Angry Index and Match Help

    A1: Joe Smith
    A2: Fran Angie
    A3: Roth Child

    D1: Transfer to Angie Fran
    D2: Transfer to Child Roth
    D3: Transfer to Smith Joe

    I am attempting to use either Index or Match function to reference to the D column cells. They are not in order and the names in the A column can be opposite to that of the D Column. I have attempted the ISNUMBER(Search) array with no success due to the reversed names.

    Thank you,
    Tricia

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Index and Match Help

    Hello and welcome to the forum.

    Based on the sample that you shared, try this in B1:

    =INDEX(D:D,MATCH("*"&MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1),D:D,0))

    or the same thing as a VLOOKUP:

    =VLOOKUP("*"&MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1),D:D,1,0)

  3. #3
    Registered User
    Join Date
    03-09-2018
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2013
    Posts
    35

    Re: Index and Match Help

    Thank you for your response!

    However, when using this function it responds with "Transfer to Smith Joe" but I am just needing "Joe Smith" retrieved.

    Thank you,
    Tricia

    ~~~~~~~~~~~~~~~~~~~~~~~

    63falcondude
    Re: Index and Match Help

    Hello and welcome to the forum.

    Based on the sample that you shared, try this in B1:

    =INDEX(D:D,MATCH("*"&MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1),D:D,0))

    or the same thing as a VLOOKUP:

    =VLOOKUP("*"&MID(A1,FIND(" ",A1)+1,LEN(A1))&" "&LEFT(A1,FIND(" ",A1)-1),D:D,1,0)
    03-09-2018, 11:43 AMSmartalekchick
    Index and Match Help

    A1: Joe Smith
    A2: Fran Angie
    A3: Roth Child

    D1: Transfer to Angie Fran
    D2: Transfer to Child Roth
    D3: Transfer to Smith Joe

    I am attempting to use either Index or Match function to reference to the D column cells. They are not in order and the names in the A column can be opposite to that of the D Column. I have attempted the ISNUMBER(Search) array with no success due to the reversed names.

    Thank you,
    Tricia

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Index and Match Help

    You said that you wanted to reference to the D column. I assumed that meant that you wanted the formula to look in column D for the name in A1 and return the correct value from column D.

    Are you saying that you want the opposite? In other words, do you want the formula to look in column A for the name in D1 and return the correct value from column A?

    If so, try this in E1:

    =VLOOKUP(MID(MID(D1,13,LEN(D1)),FIND(" ",MID(D1,13,LEN(D1)))+1,LEN(D1))&" "&LEFT(MID(D1,13,LEN(D1)),FIND(" ",MID(D1,13,LEN(D1)))-1),A:A,1,0)

  5. #5
    Registered User
    Join Date
    03-09-2018
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2013
    Posts
    35

    Re: Index and Match Help

    Yey It worked in practice. Now to switch it to my actual use for it.

    Thank you!

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Index and Match Help

    You're welcome.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  7. #7
    Registered User
    Join Date
    03-09-2018
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2013
    Posts
    35

    Re: Index and Match Help

    It worked in practice but didnt work in the application, only because the application is looking at cell A1 and searching for the match in column D. I attempted to redo the formula with that variable in mind but it keeps asking for cell reference for the formula.

  8. #8
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Index and Match Help

    it might be of more help for you to post a sample workbook so people can work on what you actually have (though leaving out confidential information).
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci

  9. #9
    Registered User
    Join Date
    03-09-2018
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2013
    Posts
    35

    Re: Index and Match Help

    For some reason it wont let me attached the file but I am needing to look up in Cell A1
    attempting to find information in Column D. As you can see names in Column D are reversed,
    Characters are not the same length, I can sit there and put mid for each space in each cell.
    My thought was to use and IF function to respond back with "yes" or "no" as to whether
    the name in A1, A2, or A3 are found in any cell in Column D.

    =If(Index(D;D,match(A1,D;D,0)=True,"Yes","No")

    However, this isnt working because the fact that the lookup column D isnt a straight
    lookup value and I am not sure whether a wild card would work or even how to put one in.

    Thank you!


    Column A Column D
    Joe Smith TRANSFER TO Child Roth REF #PW00001002 Insurance Settlement
    Fran Angie TRANSFER TO Smith Joe REF #PW0010302 personal property settlement
    Roth Child TRANSFER TO Angie Fran REF #PW000010203 Full and final settlement of any and all claims
    Attached Files Attached Files

  10. #10
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Index and Match Help

    In E1:

    =VLOOKUP(MID(SUBSTITUTE(SUBSTITUTE(D1,MID(D1,FIND(" REF",D1),LEN(D1)),""),"TRANSFER TO ",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(D1,MID(D1,FIND(" REF",D1),LEN(D1)),""),"TRANSFER TO ",""))+1,100)&" "&LEFT(SUBSTITUTE(SUBSTITUTE(D1,MID(D1,FIND(" REF",D1),LEN(D1)),""),"TRANSFER TO ",""),FIND(" ",SUBSTITUTE(SUBSTITUTE(D1,MID(D1,FIND(" REF",D1),LEN(D1)),""),"TRANSFER TO ",""))-1),A:A,1,0)

  11. #11
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.83 for Mac MS 365
    Posts
    8,480

    Re: Index and Match Help

    well I worked on this so I will forward this anyway even though 63FD solved it for you in one step.
    what I was working on needed two helper columns then a vlookup.
    helper column one is this (I put it in column B)...
    =MID(D1,SEARCH("to ",D1)+3,SEARCH("ref",D1)-14) and it pulls out the names from the text in D1 so it results in Smith Joe etc.
    this in the next column...
    =SUBSTITUTE(MID(B1,SEARCH(" ",B1),99)," ","")&" "&LEFT(B1,SEARCH(" ",B1)-1)
    this refers to the above formula results in column B and reverses the names from Smith Joe to Joe Smith
    then the vlookup I would use from there in whatever cell you want
    =vlookup(A1,$C$1:$C$4,1,false) and it would return the matches of the names of the people from the column C (if that is where you put the final name formula)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] INDEX+MATCH instead of VLOOKUP+MATCH, why is INDEX a better choice and how to re-write?
    By Renejorgensen in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-23-2016, 10:54 AM
  2. [SOLVED] Index / Match - match 3 input values and return the results from the index
    By t83357 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-08-2016, 07:34 PM
  3. [SOLVED] Match-Index in stead of Index-Match lookup Array among Arrays
    By Numnum in forum Excel General
    Replies: 2
    Last Post: 10-15-2015, 02:08 PM
  4. INDEX MATCH MATCH/OFFSET MATCH MATCH with named ranges
    By Andrew-Mark in forum Excel General
    Replies: 3
    Last Post: 02-27-2015, 10:56 PM
  5. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  6. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  7. Replies: 3
    Last Post: 05-02-2013, 01:31 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1