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

#### jbrouse

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

### 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
+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
Try this
=COUNTIFS(C:C,LEFT(\$A\$1,13),D:D,RIGHT(\$A\$1,2))

Replies
5
Views
151
Replies
1
Views
84
Replies
1
Views
190
Replies
3
Views
112
Replies
7
Views
294

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.

### 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