Lookup IP address in Subnet, return location

[christy4370]

I have 2 sheets in 1 workbook

Sheet 1

JOB ID	IP ADDRESS	Region	Site
1	172.18.04.104

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2

Display Name	Address	High Range	Region	Location(City)
VLAN MS-AD Domain Contoller	172.18.04.0	172.18.04.256	USA	City Name

<colgroup><col><col span="2"><col><col></colgroup><tbody>
</tbody>

I'm at my wits end trying to get Region and Location from sheet 2 to sheet 1.

 

[FlameRetired]

A simpler formula

=VLOOKUP(TRIM(LEFT(SUBSTITUTE($B2,".",REPT(" ",99),3),99))&".0",Subnets!$B$2:$D$14,COLUMNS($C:C)+1,0)

 

[Teeroy]

Here is a link to the file i'm working with. My goal is to get the Region and location columns from the Subnet tab to the Region and Site columns on the Discovery tab.
https://www.dropbox.com/s/b1vx8ee6g2pxhrh/testfile.xlsx?dl=0

The formula I gave you before would no longer work as you've changed the data format; you no longer have a "high range" column.

Do you intend to infer that all trailing number subnet addresses are possible for each Subnets line? If so a Vlookup can be used and @FlameRetired's formula in post#21 looks good for that.