Conditional Formatting - searching for match with string split across 2 columns

jbrouse

Active Member
Joined
Apr 28, 2005
Messages
329
Greetings,

I need some assistance with a conditional formatting formula.

In column C, I have a list of 13-digit base part numbers. In column D, I have a 2-digit location code that would get added as a suffix to make a complete 15-digit part no.

I need help to develop conditional formatting on cell A1 to highlight green if the entered full part no. exists in my list. This will require the conditional formatting formula to "combine" the values in column C and D, i.e., find the left 13 digits of the part no. in column C, and if found, if the location code found in the same row, column D matches the right 2 digits of the search value, highlight it green.

Can anyone please help?

Thank you in advance!
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
ABCD
1123456789012513
2123456789012311
3123456789012412
4123456789012513
5123456789012614
6123456789012715
7123456789012816
8123456789012917
9123456789013018
10123456789013119
11123456789013220
12123456789013321
13123456789013422
14123456789013523
Main
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1Expression=MATCH($A$1&"",$C$2:$C$14&$D$2:$D$14,0)textNO
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
13,924
Office Version
  1. 2007
Platform
  1. Windows
Try this
=COUNTIFS(C:C,LEFT($A$1,13),D:D,RIGHT($A$1,2))


 

Forum statistics

Threads
1,147,823
Messages
5,743,405
Members
423,792
Latest member
travisds

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