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?