Output only items from Column B to Column C when All 3 conditions are met in Column A

Question

I have two columns, one with account types (-A -B -C -D -E, etc) I only want Column B to be output into Column C if the account holder from Column B has all three account types (-A -B and -C)

Objective: Output Row from Column B into Column C IF Column A ends with -A, -B and -C

Here is what I have tried:

=IF(RegExMatch(A2:A,"-A") AND (RegExMatch(A2:A,"-B") AND (RegExMatch(A2:A,"-C"),"$ColumnB","No Output")

Clearly does not work and I am terrible with Google Sheets.

Answer 1

use:

=ARRAYFORMULA(IF(REGEXMATCH(LOWER(A2:A), LOWER("-a$|-b$|-c$")), 
 REGEXEXTRACT(A2:A, "(.*)-"), ))

Answer 2

You can use VLOOKUP instead REGEXP and you can build your search string with CONCATENATE

Sample:

=IF(AND(VLOOKUP(CONCATENATE(B2,"-A"), A$2:A, 1, false)<>"", VLOOKUP(CONCATENATE(B2,"-B"), A$2:A, 1, false)<>"", VLOOKUP(CONCATENATE(B2,"-C"), A$2:A, 1, false)<>""), B2, "No output")