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

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:
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.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

kasanari

New Member
Joined
May 29, 2015
Messages
12
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?
 

Brooklyn23

New Member
Joined
May 30, 2015
Messages
8
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:

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,989
Members
416,953
Latest member
broexc

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top