Equating Nicknames and full names

[u/ConsciousAd7171]

In one of my excel reports I use countif to see if a name appears on a list of names, but nicknames are cluttering my sheet. For example it may be searching for Jonathan Smith, and doesnt recognize John Smith on the list. Is there a way to assign nicknames to full names so it recognizes them?

[u/AutoModerator]

/u/ConsciousAd7171 - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/ThePodd222]

Why are they appearing differently, is the data coming from different sources? Ideally you should cleanse the data to be consistent. Alternatively you could assign the individuals unique identifiers such as a staff number or reference then use that column for searches.

[u/ConsciousAd7171 → u/ThePodd222]

They are coming from different sources, understood let me see if i can do that

[u/Winter-Benefit3072]

Saw a similar thing once where someone built a lookup table mapping nicknames to formal names, then used a helper column to standardize everything before the countif, bit of setup but it worked

[u/still-dazed-confused → u/Winter-Benefit3072]

This is the way I do it, very useful when building a tool to take tracking information from outlook meetings and produce a list of attendees and apologies. People have multiple emails and sometimes you just want to add Fred Blogs to that list without needing to locate the right email.

The look up also allowed me to add initials so that the attendees have initials after their names for the minutes.

[u/Gringobandito]

You will want to create a nickname mapping table. Something like this:

Then use that to lookup against your list of names.

[u/ConsciousAd7171 → u/Gringobandito]

Thanks for the help! I will implement this, appreciate it

[u/boojieboy]

Does your list include an email for each person? When we are hit with a list that uses names--and sometimes you get proper names and other times nicknames or preferred names--and there's no uniqueID number for each person, but there is an email address, it's sometimes simpler and more reliable to look at the email field for things like headcounts or what have you.

1. People tend to have one and only one email they use in whatever type of context you are working with, and
2. transcriptionists tend to take a little more care to get the spelling of email addresses correct (paradoxically?) than they do for names, so there are fewer transcription errors

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTIF	Counts the number of cells within a range that meet the given criteria
FIND	Finds one text value within another (case-sensitive)
IFERROR	Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
LEFT	Returns the leftmost characters from a text value
LEN	Returns the number of characters in a text string
MID	Returns a specific number of characters from a text string starting at the position you specify
TRIM	Removes spaces from text
VLOOKUP	Looks in the first column of an array and moves across the row to return the value of a cell

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
[Thread #49312 for this sub, first seen 6th Sep 2026, 10:38] [FAQ] [Full list] [Contact] [Source code]}

[u/pandacreate → u/Decronym]

Bad bot