0

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.

utp

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Dragon Kyn
  • 94
  • 1
  • 8

2 Answers2

1

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")
ziganotschka
  • 25,866
  • 2
  • 16
  • 33
0

use:

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

0

player0
  • 124,011
  • 12
  • 67
  • 124