google-sheetserror-handlinggoogle-sheets-formulaformula

Google Sheets QUERY Issue When Name Includes '


I have a QUERY that searches through names of one column and tells me which row of another column that same name occurs. This has worked perfectly for me, however I have experienced for the first time an issue where if the name contains the symbol ' it messes up the QUERY statement. Is there a work around to avoid the ' from throwing off the other 's in the statement?

This is the exact Query Below

=IFNA(ARRAYFORMULA(QUERY({A:A, ROW(A:A)}, "SELECT Col2 where Col1 = '"&B4&"' LIMIT 1", 0)), -1)

And an Example that is throwing it off is

A            |     B.        |C
Larry O'Brien| Person B      |2
Person B.    | Larry O'Brien |#VALUE!

Are there any modifications I can make to the above function to allow it to still work when names include '

I would like to just throw in that I did use VLOOKUP(MATCH()) previously however I needed an extra statement for the full logic.

My full statement would be along the lines of =IFNA(ARRAYFORMULA(QUERY({A:C, ROW(A:C)}, "SELECT Col4 where Col1 = '"&B4&"' AND Col3 = '"&D4&"' LIMIT 1", 0)), -1)

I have also tried Substitute() to get rid of the ' symbol but it does not seem to let me Substitute on "Col4" without breaking the statement


Solution

  • Alternatively you may try with:

    =xmatch(B4,A:A)