Index/match cross sheet formula to account for misspelled data entry on source sheet

I'm attempting to figure out a solution for misspellings in the source sheet that I am referencing. I get #invalid value when the source sheet has misspellings. I do not control data entry on the source sheet and we have been unsuccessful with attempts to request misspellings be corrected so I am hoping there is a way for me to adjust my formula to look for a partial match or is there a way for me to have a new formula pull in the networks names as they are added to the source sheet and have them automatically post to my sheet?

I'm using this formula currently =IFERROR(INDEX({DAI Enablement Onboarding Tracker Ratings}, MATCH([Ordering System/Reporting Name]@row + " (FAST)", {DAI Enablement Onboarding Tracker Network in MRM}, 0)), INDEX(COLLECT({DAI Enablement Onboarding Tracker Ratings}, {DAI Enablement Onboarding Tracker Network in MRM}, CONTAINS([Ordering System/Reporting Name]@row + " (FAST)", @cell)), 1))

Find more posts tagged with

Accepted answers

All comments

Matt Lynn ACT

@LaKisha DTV Interesting questions! There are formulas for finding similar matches, such as the ",0" portion of this match formula, BUT they're meant more for numeric and not words/letters or at least that's my opinion. When using words it's very specific which in some cases if not most it wouldn't know the difference between a misspelling and a intentional different spelling. You could explore creating some helper columns to pull out the words separately and do a contains() search vs an exact match. So in this case if you had two words and one was misspelled, it would find a match in the one that wasn't. The problem here would be if you ever had two instances that were actual different entries but shared a word, in that case you'd find a match that you didn't want.

If you wanted to add two helper/hidden columns for first word and second word for example you could add these formulas:

First Word: =left([sourcecolumn]@row,find(" ",[sourcecolumn]@row)

Second Word: =substitute([sourcecolumn]@row,[First Word]@row,"")

If you did that you'd then want to update your existing index match formula to something new that included has() or contains() as well as probably an if(or so it searched for the first word or the second word.

As far as the new names being available, I'm not sure what products you have but IF you had data shuttle you could maintain a master list and have that master list be continually updating a drop down so that users only are allowed to select a previously entered/approved value.

Nic Larsen

IF… if you could get the owner of the source sheet to change that column to a locked drop down menu where the user has to select from the preformatted options, then you wouldn't have all the different variables… But it sounds like that may not be an option.

The only other thing I could think of is in another sheet where you have a list of all the random ways you've seen an option written and use an index/vlookup to pull the right name back if it doesn't find a match. A lot more work and you'll likely always have a new one to add/maintain.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.