Google Sheets: Find match, check for text in adjacent cell to matched cell

user_10516570 0
Viewed 35

Example data here.

I'd like to use conditional formatting to highlight cells where, if the cell's number is found in another sheet, and there is text in an adjacent cell, it is highlighted.

So, given Sheet2:

enter image description here

When a B-column cell in Sheet1 matches an A-column cell in Sheet2, it checks if there is text in the adjacent E-column cell (in Sheet2), and highlights if there is text.

enter image description here

textjoin conditional-formatting google-sheets-formula filter regex
1 Answers

try:

=REGEXMATCH(B2&"", "^"&TEXTJOIN("$|^", 1, 
 FILTER(INDIRECT("Sheet2!A2:A"), INDIRECT("Sheet2!E2:E")<>""))&"$")

enter image description here

user_56326290
Related