I hope you guys can help me - I have been scratching my head at this for the last day.

I have a range C5:F which containers both numbers and text (its a reference number that looks similar to MAE017245). I would like to look up this range and if it matches the same text and numbers found in column S5:S, it must highlight that reference number in column S5:S to show that it is found in range C5:F.

Hope this is a quick one for you guys.

Thanks

CodePudding user response：

Update

Use this formula in conditional formatting.

=IFNA(MATCH(L5,FILTER(UNIQUE(FLATTEN($C$5:$C,$D$5:$D,$E$5:$E,$F$5:$F)), UNIQUE(FLATTEN($C$5:$C,$D$5:$D,$E$5:$E,$F$5:$F))<>""), 0),0)>1

CodePudding user response：

try:

=INDEX(REGEXMATCH(H5, TEXTJOIN("|", 1, $C$5:$F)))