[TABLE="class: outer_border, width: 20, align: left"]
<tbody>[TR]
[TD]Sheet1[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Site[/TD]
[TD]DC2[/TD]
[TD]cell not used[/TD]
[TD]cell not used[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Network Details[/TD]
[TD]IP Address[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Primary IP[/TD]
[TD]10.14.120.24[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]vLAN ID[/TD]
[TD][/TD]
[TD]cell not used[/TD]
[TD]cell not used[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Network[/TD]
[TD][/TD]
[TD]cell not used[/TD]
[TD]cell not used[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: outer_border, width: 20, align: left"]
<tbody>[TR]
[TD]Sheet2[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl66, width: 65"]Subnet[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 32"]
<tbody>[TR]
[TD="class: xl66, width: 32"]Site[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]vLAN ID[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD]Network Address[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl66, width: 65"]10.14.177.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DC 2[/TD]
[TD]246[/TD]
[TD]255.255.255.192[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl66, width: 103"]10.14.177.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]10.14.177.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl66, width: 65"]10.14.27.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DC 2[/TD]
[TD]247[/TD]
[TD]255.255.255.0[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl66, width: 103"]10.14.27.1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]10.14.27.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl66, width: 65"]10.14.121.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DC 3[/TD]
[TD]147[/TD]
[TD]255.255.255.0[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl66, width: 103"]10.14.121.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]10.14.121.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][TABLE="width: 65"]
<tbody>[TR]
[TD="class: xl66, width: 65"]10.14.122.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]DC 3[/TD]
[TD]149[/TD]
[TD]255.255.255.0[/TD]
[TD][TABLE="width: 103"]
<tbody>[TR]
[TD="class: xl66, width: 103"]10.14.122.3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 104"]
<tbody>[TR]
[TD="class: xl66, width: 104"]10.14.122.0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
these are my 2 sheets that i'm working on.
Along with your formula B2 should also be taken in consideration as we have two sites. If B2 = to DC 2 then if B4 matching 1st 3 octets of sheet2 A3:A100 then sheet1 of C4, D4, B5 & B6 should vlookup the values from sheet2 else not found.
is my query can integrated in below formula?
<tbody>
[TD="align: left"]=IFERROR(
INDEX(Sheet2!B$2:B$1000,MATCH(SUBSTITUTE(LEFT(SUBSTITUTE($A2,".","."&REPT(" ",9)),30)," ",""),Sheet2!$A$2:$A$1000,0)),"Not found"
)[/TD]
</tbody>
thanks in advance.