Find IP address in subnet range and return text

What I am trying to do is compare an IP address in a cell to a list of IP ranges and return what location the IP range is for. So for example for:

IP Address--------------- City
192.168.100.50

Column A-------------Column B-----------Column C
192.168.100.1-------192.168.100.254--------Ohio
10.10.10.1-------------10.10.10.254-------Texas

Should return for 'City' column

Ohio


I have done something similar with plain text with this:

=IF(ISERROR(MATCH($A1,$H$3:$H$604,0)),"",VLOOKUP($A1,$H$3:$J$604,2,0))

But that is looking for an exact match not in a range of column A and B. More than anything I am looking for a direction to take. Should I be working with a different function, etc., since these are IP address/numbers and I am looking in a range instead of a match. Any help is appreciated, thank you.

I am not an expert with IP addresses.. but I thought perhaps if you remove all the dots and convert to a number, you can use that to compare.

I also assume your fogot a .100 in the B column (first entry)

So if your input is in F2, then try:

Please Login or Register  to view this content.

adjust ranges to suit.

I guess I didn't think clearly initially... we will have to see if there is a better way...

Just another guess... but if you have your database sorted in ascending order by column A (the start IPs)... does this work?


=LOOKUP(F2,$A$2:$C$8)

where F2 contains the lookup IP and A2:C8 the database.

VegasL,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.