Right function inside a Match function - highlight based on criteria (Excel 2007)

[Brooklyn23]

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)

<tbody>
</tbody>

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.

 

[kasanari]

Hello,

Sorry I'm not 100% positive what you're looking for, but this might help:

Conditional formatting formulas need to result in either true or false, so you need to compare the result against something.

The formula you need within the conditional formatting box is =match(right($D3,7),$E$6:$E$50,0)<>0

Are there only leading 0s in the D column and all the numbers in the E range are only the 7 digit numbers?

 

[Marcelo Branco]

Try

=MATCH(RIGHT($D3,7),RIGHT($E$6:$E$50,7),0)

M.

 

[kasanari]

Yeah actually it doesn't need the <>0. His formula works too

 

[Brooklyn23]

Hello, I will be back on tonight and supply all the information asked for. Thank you for helping me.

LINKBACK URL Right function inside a Match function - highlight based on criteria

 

[Brooklyn23]

Hi all. I'm including a link so you can view the actual problem.

I've tried the suggested solution, but I'm not getting any luck. My best guess is that the right function doesn't work on a lookup_array. Perhaps you could view the image of the problem. Thank you.

IMAGE OF THE PROBLEM: http://1drv.ms/1crWwQJ

 

[Brooklyn23]

Hello. In order to follow proper etiquette, further discussion on this matter will be consolidated to one forum: Right function inside a Match function - highlight based on criteria.

Kasanari, marcelo branco, I hope you can join the discussion in the new forum. Thank you.