Which text function to use to look up name in different coloums with different layout

[NicGo2512]

Hi, I'm looking for which text or logical function to use to look up a name in different columns with a different layout. e.g. one column contains only the last name and the other column contain the last name with the initials and also some names are upper case and some are not

1. Smith
2. J.A. Smith

I want to see if in both lists the names are available and if so I need to add an lookup function to return a value from the other list.

Is there is a function that cuts of J.A. (not right function because list contain names with different lengths)?

Can anybody help me with that?

BR Nicole

[#2]

@NicGo2512 

There are functions that could help, but with what you've written I can only point you in their direction. You discount RIGHT, "because list contain names with different lengths" but have you considered combining RIGHT with LEN and FIND?

For example, with your example of "J.A. Smith" in cell A1, this formula in B1

=RIGHT(A1,LEN(A1)-FIND(" ",A1))

delivers the result "Smith"  and it would also work with "A.B. Houghnagel"

But of course, that works in part because I know that there's only one space in A1. If your actual files are inconsistent, which is more likely, you'd have to come up with more sophisticated formulas.

Can you post a sample of your spreadsheets (without real names, but representative of the variety with which you're dealing)?

[#3]

Hi@mathetes 

Thank for you quick reply and input. I'm gonna try what you suggested. I attached an example of the file.

What I need to check is the names in sheet1 are also in sheet2 if so, than I need to lookup the value that is in Sheet2 column C. Maybe this brings up some more ideas. :) Appreciate your help thank you.

[#4]

@NicGo2512 

I'll try to look at that later today. In the meantime, it's quite likely that somebody else here in this techcommunity can give you the solution. I know I've seen similar problems solved before...you might be able to search for them using various search terms like "inexact match" or the like.

I also am compelled to offer the observation--realizing that you yourself may be in the unfortunate position of working with sheets that somebody else designed; I've found myself in that frustrating position more than once--that a database with names should ALWAYS have those names stored as FIRSTNM, MIDDLE, LASTNM, PREFIX, SUFFIX, i.e., as separate columns (separate cells)...  Doing this enables not only what you're trying to do here, but also is the most practical way to enable people to produce, let's say:

• name cards that appear as John Q. Public
• attendance lists showing Public, John Q.
• address labels sent to Mr John Public, Esq.
• and so forth

It's part of good database design. So please pass on that advice to whoever created those sheets in the first place.

And now I'll step down from my soapbox.

[#5]

@NicGo2512 

Changed your Sheet2 into a structured table to make the formula more dynamic. Extend the table and the formulae will follow. The looking up of partial names is done by INDEX and SEARCH, but it only finds the first occurrence of the name. It can NOT differentiate between "A. Smith" and "B. Smith" based only on a search for "Smith".

So, it might not be useful. But perhaps it will. See attached!

[#6]

Thanks a lot @Riny_van_Eekelen! I'm gonna try this and see if it helps me.