I have two column ranges: C410:C1100 and M5:M100. I want to iterate through the C range, one cell at a time and try to find at least one match in the entire M range. If a match is found, I want to color the background of the cell in the C range yellow or something (honestly the format isn't that important).
I have struggled to find a solution to do this via a formula. I thought about VLOOKUP but wasn't sure.
Please try selecting ColumnC HOME > Conditional Formatting, New Rule..., Use a formula to determine which cells to format, Format values where this formula is true::
=MATCH(C1,M$5:M$100,0)
Format..., select yellow, OK, OK.
Go to Manage Rules and limit the Applies to range as you wish.