Case-Sensitive Name Checker

user_1694426 0
Viewed 63

I need help with an arrayformula that can look or check for names in a list based from a separate list of names.

The important part is it has to be case-sensitive and should return a "Not Found" error if it cannot find an exact match from a separate list.

Case-Sensitive Name Checker

In the sheet above, the separate list is in column F and it needs to check column B. The expected output is in column C. For row 3, "sean massey" should return a "Not Found" error because it can't find an exact case-sensitive match from column F.

IMPORTANT: There is an additional condition to this as well. I need the arrayformula to only be in cell D2 only and it will process the names in column B from top to bottom where column A is not empty.

Here is an editable sheet link anyone can use: https://docs.google.com/spreadsheets/d/1F8KaK3NqhYrUEM9Eo7VYl0DqLSvguJbHH81mY4dF-8U/

Thank you very much for any assistance.

array-formulas google-sheets-formula google-sheets if-statement regex
2 Answers

regex is case sensitive. delete everything in your column and use in row 2:

=INDEX(IF(B2:B="",,IF(REGEXMATCH(B2:B, 
 TEXTJOIN("|", 1, F2:F)), "Found", "Not Found")))

enter image description here

update:

=INDEX(IF(B2:B="",,IF(REGEXMATCH(B2:B, 
 "^"&TEXTJOIN("$|^", 1, F2:F)&"$"), "Found", "Not Found")))
user_56326290

=ARRAYFORMULA(IFS(A2="","",OR(EXACT($F$2:$F,B2)), "Found", TRUE, "Not Found")) should work.

The first IFS condition checks if column A is empty. Then, the ArrayFormula is used with EXACT for case-sensitivity and OR to consolidate the resulting array into one Boolean value.

user_158183490
Related