Formula for comparing multiple columns

Occasional Reader

Good afternoon 

 

I'm struggling to get formulas to work for me when comparing two data sets. There are four columns to compare and wanting to find if there is a matching "set" in another range of data

 

So I've been trying to do as a two step process where it checks whether the information in Data Set 1 is in Data set 2 unless concat to create unique strings, however what I also require is it then to state if there is no match, is there a match on say 3/4 items and then what is wrong if so. 

 

Understand this might not be possible but wanted to see if anyone else had any suggestions2024-09-17_10h08_21.jpg

1 Reply

@CorinneDinning 

In F3:

=LET(m, BYROW($I$3:$L$7, LAMBDA(r, SUM(--(r=A3:D3)))), IF(MAX(m)=4, "MATCH", "NO MATCH"))

Adjust the range as needed. Then fill down.

 

The other one is complicated: one row on the left could have 3 matches with multiple rows on the right - one row with a mismatch on code, another row with a mismatch on start date...