Get matches from A B rows

Hello friends of this forum, I haven't bothered you with my stories for a long time, but I have a question to program a function in Excel and so, I came to this site from which I have always obtained the best and most professional answers.

As always and in advance I thank you for all your help and the time you give me, if my doubt is not resolved here, I am convinced that I will not obtain it elsewhere.

And of course I have tried, and I share with you the code that I have done, but it doesn't work, and the *xlsm document where you can see a small table and a brief explanation of what the function should do and I can't get it to do.


Let's go to trouble


A Function named "matches"
A table like this with two columns A and B and down up to 45500 rows

A B C
411 203 no_match
411 268 no_match
411 342 472
411 378 no_match
417 309 no_match
417 339 no_match
437 321 no_match
437 411 no_match
451 242 no_match
451 342 no_match
451 413 no_match
457 203 no_match
457 435 no_match
459 342 no_match
459 203 no_match
459 420 no_match
462 371 no_match
462 459 no_match
472 342 no_match
472 411 no_match
477 217 no_match
477 203 no_match

We call the function from any cell in column C, for example let's start from C2

The Function start the searh with A2 value and look for this value ONLY IN COLUMN B
If value is found in other place or places in B column keeps in memory the value of the cell in the left of the found value, it means, the value in Column A. Following with the example we could find the A2 value(411) in B9 and in B21 and keep in memory the values (A9=437 and A21=472)

Now we start the second searh with B2 value and look for this value ONLY IN COLUMN B, (but never look for it in the row where we called the function in this case function is called from C2 then row2 is out of search this time). If value is found in other place or places in B column keeps in memory the value of the cell in the left of the found value, it means, the value in Column A. Following with the example we could find the B2 value(203) in B13, B16 and B23 and keep in memory the values (A13=457, A16=459 and A23=477)

Now compare the values saved in memory from the search with A2 (437 and 472) and the values saved in memory from the search with B2 (457, 459 and 477). If some value in the group from A2 share the same value with the others in the group from B2 a match is found and the function put this shared value in C2, but in this case as can be seen, no match is found, then the program can show in C2 "0" or "no_match" or any other message to show that there is no parity.

Now if the same search is performed in C4, we can see than A4 value = 411 and B4 value = 342 are sharing one value in A column
for A4 = B9 = B21 = 411 and B9 in A9 = 437 and B21 in A21 = 472
for B4 = B11 = B15 = B20 = 342 and B11 in A11 = 451, B15 in A15 = 459 and B20 in A20 = 472
So a match is found, the number 472 exist in both groups from the two initial numbers A4 and B4, then the function put number 472 in C4.

When I call the function from any cell in column C, the answer is always the same "#?VALUE!" and never find a match but really exist in this table.

Due to the characteristics of this table, we don't get two or more coincidences with the row values of A and B. This means that a group formed by A, B and C will be unique and will not be repeated.



And this is the Function in which I've been working during some days and due to my little knowledge to program in this environment, I am unable to find where is the mistake so that the code works as it has to do.

Function matches()
    Dim cellAB As Variant, cellBB As Variant
    Dim searchValueA As Variant, searchValueB As Variant
    Dim matchValueAB As Variant, matchValueBB As Variant
    Dim matchCellAB As Variant, matchCellBB As Variant
    
    searchValueA = ActiveCell.Offset(0, -2).Value
    searchValueB = ActiveCell.Offset(0, -1).Value
    
    For Each cellAB In ActiveCell.Offset(0, -1).EntireColumn
        If cellAB.Address <> ActiveCell.Address Then ' Exclude current cell
            If cellAB.Value = searchValueA Then
                Set matchCellAB = cellAB
                matchValueAB = matchCellAB.Offset(0, -1).Value
                Exit For
            End If
        End If
    Next cellAB
    
    For Each cellBB In ActiveCell.Offset(0, -1).EntireColumn
        If cellBB.Address <> ActiveCell.Address Then ' Exclude current cell
            If cellBB.Value = searchValueB Then
                Set matchCellBB = cellBB
                matchValueBB = matchCellBB.Offset(0, -1).Value
                Exit For
            End If
        End If
    Next cellBB
    
   
    If matchValueAB = matchValueBB Then
        ActiveCell.Value = matchValueAB
    Else
        ActiveCell.Value = "no-match"
    End If

End Function

Thanks in advance to all of you who in one way or another can help me debug my code so that this function can runs correctly in my spreadsheet.

Hi Serena. Try with:

Originally Posted by SerenaCruz

Now I tick the post as SOLVED, your contribution has helped me enormously. I wish you the best in your full life.

Frankly, Serena, you have been very kind and I wish the same for you.