I want to highlight/match between column C,D & K,L. but there is a catch. As you can see on Green marked area B&C ""13710 36"18"" (they are a pair) must match in the list of K,L ""13710 36"18"" column as a pair.


so Green marked pair C,D matches with K,L.




But in K,L there is another pair RED ""13710 44"18"" that doesnt match with any pair in C,D and RED ""13710 32"18"" C,D is not available in K,L


so i want to highlight those who matches as a pair. which pair doesnt match wont highlight (or highlight vice versa)


matching will be done between this four colums

Attached Images

TEST.png (81.4 KB, 16 views)

Attached Files

MATCH HIGHLIGHT.XLSX (32.6 KB, 4 views)

Perhaps a CF like applied to data in col B and C ? If you need the same in K and L adapt the formula to that range

Attached Files

MATCH HIGHLIGHT.XLSX (32.9 KB, 3 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

this solution works. but has a problem even if B6 C6 is empty it says true. And if C6 is partially matches it says true. like
$K$6:$L$157 range has "13710 44"18" and TEXTJOIN("",,$B6,$C6 "13710 44"1" ("8" missing)

OK try as CF

Attached Files

MATCH HIGHLIGHT.XLSX (32.9 KB, 5 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

now everything is false.

Attached Images

TEST.png (53.1 KB, 8 views)

I would appreciate
1.first telling me if what I proposed is what you want, even partially ( a small " thank you" does not hurt)
2. attaching a sheet instead of a useless picture would spare me some time
3.As you can see in the attached it works perfectly and, no, not "everything is FALSE"

Attached Files

MATCH HIGHLIGHT(2).XLSX (36.7 KB, 4 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

sorry for the mistake. thank you very much for your time and talent spend on me.


it seems i had to ctrl+shift+enter. i didnt know i had to do that. u didnt specify {}.


thank you very much.

This is right and wrong at the same time
It is right when you use the formula in a spreadsheet ( using 365 myself, I often assume everyone does..)
However, it is wrong when you use it in a CF as in that case the formula is automatically treated by XL as an array formula in all versions

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

A little suggestion to make things more robust. It doesn't matter with your sample data but it might matter with different data.
In columns B&C you might have:
1381 and 38"24
and in columns K & L you might have somewhere:
13813 and 8"24
When the two pairs are concatenated they both look the same:
138138"24
but they're not actually a match but the current formula would say it is a match.
That can be corrected by changing from:
to:
Now one looks like:
1381¬38"24
and the other:
13813¬8"24
so they aren't a match any more.

It doesn't have to be that ¬ character, a space would be almost as good; any character that you wouldn't expect to see in the data would be best.