Match IP address to Office Location

[griam01]

I am trying to write a formula that will match IP Addresses to a range which will name an office location. The problem I am having is not all the ranges are the same.

Below is an example. Column A is the information I will have. Column B is the location I want it to return. Column C is my notes explaining what it should match. Columns F and G is how it should match.

I have been all over many forums and can't find a way to match this as I would need it due to the IP range differing each time. Any assistance would be GREATLY appreciated!!! Thanks in advance for your time.

I tried to attach an actual excel but the attach button doesn't bring up a window no matter what browser I tried to use.

Capture.JPG

[Ash_Maverick]

I hope this helps........

Try putting a .(dot) at the end of all those IP in column F which are not complete...........then use this formula in column B...........=VLOOKUP(A2,F:G,2,1)

[Ash_Maverick]

I think it wont work for REMOTE locations

[Ash_Maverick]

did you try........Fuzzy Lookup Add-In ?

[griam01]

It is close, but it did not work for the remote locations. Even if the remote locations come up N/A, at least I could fill those in easier. For example for 10.0.0.10 it came back with Office 1. I did not try Fuzzy Lookup add in. Let me looks some info up on it

[xlnitwit]

Hi,

If you can add a trailing full stop to the numbers in column F that are not full IP addresses, then you might use:

=IFERROR(LOOKUP(1,MATCH($F$2:$F$10&"*",A2,0),$G$2:$G$10),"Remote")

[Ash_Maverick]

wow........i think that would work

[griam01]

YAY!!! Thank you very much!!! That worked. I really appreciate all of your help!!!