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
Alternatively you may try with:
=xmatch(B4,A:A)