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

#### Brooklyn23

##### New Member
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.

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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?

Try

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

M.

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

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

Last edited:

Replies
1
Views
302
Replies
6
Views
753
Replies
1
Views
731
Replies
3
Views
521
Replies
2
Views
305

1,218,899
Messages
6,145,098
Members
450,590
Latest member
Naneng

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back