Returning customers ID from their first, middle and surname

[angeliccharm]

Hi, hope you're well. As the title says, what formula would I use to return a customers ID which looks at their first, middle, and surname individually? Within the sample data, there are also some data which aren't in the same format e.g., for A11 (small comm) would not be necessary.



As of now, I am using the formula for a partial vlookup: =VLOOKUP("*"&A2&"*",'Data - FULL DUMP'!H1:$AF$46708,25,FALSE) for column F to bring back ID 2. However, I would like a formula that looks at column B, C and D which also takes into account any random text that may come after each part of the name.

To bring back the first, middle and last name I have used the following:
Column B: =LEFT(A2,FIND(" ", A2)-1)
Column C: =VLOOKUP(A2&"*",'Data - FULL DUMP'!$H$1:$L$46708,5,FALSE)
Column D: =RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2," ","*",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))))

I've also tried using the formula: =VLOOKUP("*"&B2&"*"&C2&"*"&D2&"*", Data - FULL DUMP'!H1:$AE$46708,24,FALSE) but it brings back N/A so not sure if there's something wrong with my formula?

Thank you!

 

[awoohaw]

im confused with your post.
Why are you looking up values when the values are already there?

Why are you looking up something (column 5) may or may not have any thing to do with the name in Column A as the middle name?

 

[Peter_SSs]

Welcome to the MrExcel board!

I suggest that you provide a small set of sample dummy data and explain again in relation to that sample.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.