VLOOKUP when text contains special characters

Chris Russell

I must be missing something obvious, because I can't find any other posts dealing with this issue. Here's my scenario:

Name Audit Sheet: this sheet contains a column of unique project names that will change (it's not static)

Budget Sheet: this sheet has project budget information, one row per project. The project name in each row should be static, but the other data in the row will get changed over time.

Budget Sheet has a "Helper: Name Check" column that takes the project name in the row and does a VLOOKUP on the Name Audit Sheet for it. If it doesn't find a match then it returns "No Match" in the Helper column. I then use the Helper column to shade the project name cell red, indicating that the project name the user entered is not valid.

In other words, if the project name entered on the Budget Sheet does not match a project name in the Name Audit Sheet, then the cell gets shaded red to indicate an invalid project name.

This works fantastically ... until a project name has special characters such as apostrophes or parentheses. It is not an option to rename the projects without special characters. Project names don't use any type of naming convention, so I can't do a formula that relies on them being a certain length or the like.

Is there a way to accomplish the VLOOKUP or something similar when the lookup range will have some text with special characters in them?

Kelly Drake

INDEX/MATCH is more optimized for Smartsheet and I'd recommend transitioning to that formula where you can.

Just to make sure I'm understanding here's your set up:

Sheet A - Column 1 = Project Name

Sheet B - Column 1 = Project Name

Sheet B - Column 2 = Helper Project Name Validation

There are a handful of options you could use on your budget sheet....

Note: {Name Audit Project Name} is the cell reference to solely the Project Name column in your Name Audit sheet.

=INDEX({Name Audit Project Name}, MATCH([Project Name]@row, {Name Audit Project Name}, 0))

This brings you the Project Name from the other sheet when it matches the project name on your budget sheet. Then you can set up the conditional formatting when the cell returns #NOMATCH

I hate seeing the hashtag errors so if that formula works you may want to consider doing something like ...

=IFERROR(INDEX({Name Audit Project Name}, MATCH([Project Name]@row, {Name Audit Project Name}, 0)), "Confirm Project Name")

or if you change your column type to the R/Y/G balls you could do soemthing like this:

=IF(INDEX({Name Audit Project Name}, MATCH([Project Name]@row, {Name Audit Project Name}, 0)) =[Project Name]@row, "Green", "Red')

Chris Russell

Thank you so much for the quick and thorough response! I will post again (soon) once I've had a chance to try this.

Chris Russell

@Kelly Drake , thank you again for the guidance! This is working exactly how I want it. I added one additional piece to your formula to handle blank cells:

=IF(ISBLANK(Project@row), "Blank", IFERROR(INDEX({Project Name Audit}, MATCH(Project@row, {Project Name Audit}, 0)), "No Match"))

Also, thank you for the tip on using INDEX/MATCH instead of VLOOKUP. I will make a point of doing that going forward.