Partial IP Address Range Match Between 2 Columns

Hello,

I have a list of banned IP addresses in column A named "PK" listed as 3 quadrant addresses (123.456.789 or 1.25.289)
I have a list of all my message board IP addresses in column B named "IP" listed as 4 quadrant addresses (123.456.789.101 or 1.1.0.5)

How can I make a formula that compares each IP address in column B to each IP address in column A?

Even better, how can I have any matches copied to column C?

Do you have an example workbook with some sample data in?

Sure, thank you. Hopefully, I have attached the file correctly.

Yup, it's fine, thanks.

In cell C2 try this:

=IF(ROW(A1)>SUMPRODUCT(--(ISNUMBER(MATCH(LEFT(IP,FIND("~",SUBSTITUTE(IP,".","~",3))-1),PK,0)))),"",INDEX(B:B,SMALL(IF(ISNUMBER(MATCH(LEFT(IP,FIND("~",SUBSTITUTE(IP,".","~",3))-1),PK,0)),ROW(IP)),ROW(A1))))

This is an array formula, so must be entered with Ctrl-Shift-Enter, not just Enter.

Once entered copied down as many rows as you feel you need.

Note that the data in your sample file doesn't appear to include any matches.

I entered that formula and did not get any matches. Then I changed the data in cell A2 to match the data in B2 except for the last quadrant and still did not get a match...

Did you enter the formula with Ctrl-Shift-Enter? If so it should appear on the formula line encased in curly brackets { }

The data entered in the cell, but with no curly brackets show. I tried 10 times - am I doing this correctly?

1. highlight the range of cells where I want my formula to appear;
2. enter the formula in the edit bar;
3. press ctrl+shift+enter (simultaneously)

At step 1 try:

1. Highlight a single cell where you want the formula to appear.

Then after that it should work.

That also did not work for me. If I directly enter the formula by pasting it into the cell, it shows in the edit bar, but the cell remains blank. If I enter the formula into the edit bar then use CSE, the formula shows, but is not in curly brackets.

@martindwilson: if I take any value in the A column and edit it to match the first 3 quadrants of the B column (in the same row, in this case just for ease) I would expect a match, wouldn't I?

Hmmm, not really sure what to advise.

I've attached my updated version of your sample sheet, with the formula and a couple of matches in, but I don't know why it's not working for you.