Partial Lookup/Match

  • Thread starter Thread starter Kieran
  • Start date Start date
K

Kieran

I have a series of ip address and I want to run a query
in Excel to match it with a subnet.

For example: if i have an address of 10.100.128.10 and I
know that the subnet 10.100.128 is the Head Office site,
how can I get Excel to query the IP address and come back
with the address. I know Vlookup's but I don't know how
to search on only part of the address.

Please help
 
One way

=VLOOKUP(A1,LEFT(C5:D200,10),2,0)

where A1 is the cell where you put 10.100.128
or

=INDEX(D5:D200,MATCH(TRUE,ISNUMBER(FINDA1,C5:C200)),0))

both formulas entered with ctrl + shift & enter
 
...
...
or

=INDEX(D5:D200,MATCH(TRUE,ISNUMBER(FINDA1,C5:C200)),0))
Click to expand...
...

I think you made a typo, so

=INDEX(D5:D200,MATCH(TRUE,ISNUMBER(FIND(A1,C5:C200)),0))

but couldn't this match ###.10.100.128 rather than 10.100.128.###? You could
rewrite it as

=INDEX(D5:D200,MATCH(TRUE,ISNUMBER(1/(FIND(A1,C5:C200)=1)),0))
 
Harlan Grove said:
...
..
..

I think you made a typo, so

=INDEX(D5:D200,MATCH(TRUE,ISNUMBER(FIND(A1,C5:C200)),0))

but couldn't this match ###.10.100.128 rather than 10.100.128.###? You could
rewrite it as

=INDEX(D5:D200,MATCH(TRUE,ISNUMBER(1/(FIND(A1,C5:C200)=1)),0))
Click to expand...

Clever
 
Back
Top