FIND REPLACE SUBSTITUTE I am not sure which one please help

[neilclark]

Hi,

Hoping i might be able to get some assistance here, I have been googling most of the evening and am not able to find the answer to solve my issue.

In my spreadsheet i have a column of data in column B in sheet two which contains IP subnet addresses and names as follows

B4:192.168.1.0/24 C4:Zone1
B5:192.168.2.0/24 C5:Zone2
B6:192.168.3.0/24 C6:Zone3
B7:192.168.4.0/24 C7:Zone4
....etc

And on sheet 1 i have a column of cells that contain entries inputed by the user like below
192.168.1.5
192.168.3.10
192.168.4.155

So far i have managed to write a vlookup function that looks up the user inputed cell and matches the entry on sheet 2 and displays the zone info. however this only works when i enter an exact match i.e. 192.168.1.0/24.

I need to be able to match on the first three numbers and disregard the last bit.

This is what i have so far =VLOOKUP(I9,'IP-Lookup'!B:C,2,0)

I cannot figure out this part and its driving me nuts, anyone have any ideas.

Thanks very much hope this all makes sense.

Cheers

Neil

[ben_hensel]

Are you expecting a unique result, or can there be more than one? If there are more than one, do you want to return the first result, all of them, or what?

[neilclark]

Are you expecting a unique result, or can there be more than one? If there are more than one, do you want to return the first result, all of them, or what?

Hi Ben,

Yes the result from the look up should only ever be unique.

Thanks

Neil

[ben_hensel]

In that case it's pretty straightforward.

Please Login or Register  to view this content.

That's an array formula, so confirm with CTRL+SHIFT+ENTER, not just ENTER key.

Note that this searches the first nine characters, not the first three period-separated values; there's no difference based on the example data that I can see, but it's a faster & simpler operation that hunting out the fourth period and cutting off from there forward.