Brooklyn23
New Member
- Joined
- May 30, 2015
- Messages
- 8
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:
[TABLE="width: 71"]
<tbody>[TR]
[TD]MATCH(RIGHT($D3,7),$E$6:$E$50,0)[/TD]
[/TR]
</tbody>[/TABLE]
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.
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:
[TABLE="width: 71"]
<tbody>[TR]
[TD]MATCH(RIGHT($D3,7),$E$6:$E$50,0)[/TD]
[/TR]
</tbody>[/TABLE]
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.