I'm using a formula to run through a list of names, compare them to a master list and check associated values for the names that appear on both.=COUNTA( Iferror(QUERY(Requirements!A2:F, select A where A matches ' & JOIN(|, Attendance!A2:A) & ' and B = 'Yes', 0),))However, some of the names contain apostrophes (eg. O'Malley) and this is killing the formula - Is there a simple way to escape or replace the apostrophe in the name either in the original lists or as the formula reads it?
Google Sheets - How can I stop apostrophes in names from tripping up formulae?
google spreadsheets;formulas;special characters
According to Google's Query Language Reference, string literals should be enclosed in either single or double quotes. Examples: fourteen 'hello world' It's raining.You are joining the Attendance values by | only, which again is enclosed in 's, so if Attendance!A2:A wasLarssonO'MalleyTaylorthe result of the ... ' & JOIN(|, Attendance!A2:A) & ' ... would be 'Larsson|O'Malley|Taylor'.As you can see, this leads to unmatched ' - the string seemingly ends and begins with a ', but the apostrophe in O'Malley shortcuts the string so that it is really 'Larsson|O'.You could use  instead of ' when creating the string:=COUNTA( Iferror(QUERY(Requirements!A2:F, select A where A matches  & JOIN(|, Attendance!A2:A) &  and B = 'Yes', 0)))Note how the double quotation mark  allows the character to be escaped, so it does not short-circuit the string.This would allow O'Malley to be counted, but would be trouble if you have a name that includes . To avoid all sorts of character issues, your formula should be rewritten so that it does not rely on creating a query by concatenating input strings.