Index and Match Help

1. 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. 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. Re: Index and Match Help

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. 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. Re: Index and Match Help

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

Thank you!

6. Re: Index and Match Help

You're welcome.

7. 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. 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).

9. 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

10. 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. 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)

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

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