Hi all,
In a nutshell, I need to apply conditional formatting to a row (or multiple cells within a row if easier) using a match function, but only looking at the last 7 characters of each string (lookup value and lookup array)
I have an ID column in data set 1, and an ID column in data set 2. If any of the ID's in ID column data set 2 appear in ID column data set 1, then I want to highlight in data set 1 the row that the match is found. I can put these data sets on the same sheet, different tabs, or even different workbooks, whatever makes it easier.
Challenges: The ID's could have letters inside, and could have a leading zero, so they must be changed to text. For a given ID match, the leading zero may only be present in one of the instances.
I'm close, but I'm not solving the leading zero problem. I'm having difficulties combining a right function with the lookup array (Inside the MATCH function).
This is the conditional formatting formula that seems close to working:
MATCH(RIGHT($D3,7),$E$6:$E$50,0)
Obviously something needs to also indicate to take the RIGHT,7 for the lookup_array as well. I'm not sure if RIGHT is even working properly when within the MATCH function though.
Please help. Thank you.
Bookmarks