Forum Discussion
Which text function to use to look up name in different coloums with different layout
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)?
Himathetes
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.
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!
Thanks a lot Riny_van_Eekelen! I'm gonna try this and see if it helps me.
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.
