searching within a range

Originally Posted by jcbarnes

I don't know if this is possible or not in Excel.

I have multiple ranges of IP addresses (lower and upper end address for each range).

Is there some way to create a macro or app that would accept a specific address, search to see if it falls within any of the ranges, then return information on the range if it does? Or should I be looking toward some other application to do this in?

Thanks!

Hi,

I think a macro would be required, or at least a better formula, but, with your 'from' (as 10.11.12.255 etc) in column A, your 'To' in column B, and your test address in C1, in D1 put

Please Login or Register  to view this content.

which is

=IF(AND(MID(SUBSTITUTE(C$1,"."," "),45,50)*1+256*(MID(SUBSTITUTE(C$1,"."," "),30,15)*1+256*(MID(SUBSTITUTE(C$1,"."," "),10,15)*1+256*(LEFT(SUBSTITUTE(C$1,"."," "),3)*1)))>=MID(SUBSTITUTE(A1,"."," "),45,50)*1+256*(MID(SUBSTITUTE(A1,"."," "),30,15)*1+256*(MID(SUBSTITUTE(A1,"."," "),10,15)*1+256*(LEFT(SUBSTITUTE(A1,"."," "),3)*1))),MID(SUBSTITUTE(C$1,"."," "),45,50)*1+256*(MID(SUBSTITUTE(C$1,"."," "),30,15)*1+256*(MID(SUBSTITUTE(C$1,"."," "),10,15)*1+256*(LEFT(SUBSTITUTE(C$1,"."," "),3)*1)))<=MID(SUBSTITUTE(B1,"."," "),45,50)*1+256*(MID(SUBSTITUTE(B1,"."," "),30,15)*1+256*(MID(SUBSTITUTE(B1,"."," "),10,15)*1+256*(LEFT(SUBSTITUTE(B1,"."," "),3)*1)))),"y","n")

and formula fill down the extent of your ranges. (note the 15 spaces as defined in the Code area)

Hope this triggers some better ideas.

---