IF / VLOOKUP with partial text matching from a cell

[kkrishna]

Dear All,
I was look for a formula if a cell text is matching partially then vlookup from another sheet for matching value.

Cell A 10.253.19.7

in Cell B & C, the formula should have if Cell A 10.253.19.* then vlookup the value from sheet2 and display Subnet mask & gateway respectively.

Sheet2 has table as below:

Subnet	Subnet Mask	Gateway	VLAN
10.15.198.	255.255.254.0	10.15.198.1	201
10.253.19.	255.255.255.0	10.253.19.1	301

<tbody>
</tbody>

Your assistance on this regards greatly appreciate.

Thank you in advance.

 

[louisH]

	A	B	C
1	IP	Subnet mask	GateWay
2	10.253.19.7	=IF(SUBSTITUTE(A2,"10.253.19.","")<>A2,VLOOKUP(C2,Sheet2!A1:D3,2),"")	=IF(SUBSTITUTE(A2,"10.253.19.","")<>A2,VLOOKUP(C2,Sheet2!A1:D3,3),"")

<tbody>
</tbody>

 

[Peter_SSs]

Try this. Adjust ranges to suit your data.

Book1
	A	B	C	D
1	Subnet	Subnet Mask	Gateway	VLAN
2	10.15.198.	255.255.254.0	10.15.198.1	201
3	10.253.19.	255.255.255.0	10.253.19.1	301
Sheet2

Formula in B2 below is copied across and down.

Book1
	A	B	C
1	IP	Subnet Mask	Gateway
2	10.253.19.7	255.255.255.0	10.253.19.1
3	10.18.255.6	Not found	Not found
4	10.15.198.200	255.255.254.0	10.15.198.1
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(INDEX(Sheet2!B$2:B$1000,MATCH(SUBSTITUTE(LEFT(SUBSTITUTE($A2,".","."&REPT(" ",9)),30)," ",""),Sheet2!$A$2:$A$1000,0)),"Not found")

 

[kkrishna]

louisH & Peter_SSs for your prompt response. I'm so pleased with your kind attention on my request.

 

[kkrishna]

Sheet1

<tbody>
</tbody>

1	A	B	C	D
2	Site	DC2	cell not used	cell not used
3	Network Details	IP Address	Subnet Mask	Gateway
4	Primary IP	10.14.120.24
5	vLAN ID		cell not used	cell not used
6	Network		cell not used	cell not used

<tbody>
</tbody>

Sheet2

<tbody>
</tbody>

1	A	B	C	D	E	F
2	Subnet <tbody> </tbody>	Site <tbody> </tbody>	vLAN ID	Subnet Mask	Gateway	Network Address
3	10.14.177. <tbody> </tbody>	DC 2	246	255.255.255.192	10.14.177.1 <tbody> </tbody>	10.14.177.0 <tbody> </tbody>
4	10.14.27. <tbody> </tbody>	DC 2	247	255.255.255.0	10.14.27.1 <tbody> </tbody>	10.14.27.0 <tbody> </tbody>
5	10.14.121. <tbody> </tbody>	DC 3	147	255.255.255.0	10.14.121.3 <tbody> </tbody>	10.14.121.0 <tbody> </tbody>
6	10.14.122. <tbody> </tbody>	DC 3	149	255.255.255.0	10.14.122.3 <tbody> </tbody>	10.14.122.0 <tbody> </tbody>

<tbody>
</tbody>

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?

=IFERROR(INDEX(Sheet2!B$2:B$1000,MATCH(SUBSTITUTE(LEFT(SUBSTITUTE($A2,".","."&REPT(" ",9)),30)," ",""),Sheet2!$A$2:$A$1000,0)),"Not found")

<tbody>
</tbody>

thanks in advance.

 

[Peter_SSs]

For your new sample data ...

1. Do cells A5 and A6 on Sheet1 have anything to do with getting the correct result? If so, what?

2. What are the expected results for that sample data and what cells and what sheet do those results go in?

3. Are there any more complications to be added in after this?

 

[kkrishna]

Hi Peter,

A2 to A6 from sheet1 are headers of the results. A5 is vLAN ID associated with that subnet & A6 is network address (ex: 10.14.120.0)
B4 is Manual entry
B5, B6, C4 & D4 should be populated using vlookup or any other integer from sheet2
If I manually input B4 (ex: 10.14.120.24)


on Sheet1, If B2=DC2 & B4="10.14.120." then B5, B6, C4 & D4 fillup automatically from sheet2 by matching the B and A columns of sheet2

no more complication after this.....

 

[Peter_SSs]

See how this goes.

Excel Workbook
	A	B	C	D	E	F
1
2	Subnet	Site	vLAN ID	Subnet Mask	Gateway	Network Address
3	10.14.177.	DC 2	246	255.255.255.192	10.14.177.1	10.14.177.0
4	10.14.27.	DC 2	247	255.255.255.0	10.14.27.1	10.14.27.0
5	10.14.121.	DC 3	147	255.255.255.0	10.14.121.3	10.14.121.0
6	10.14.122.	DC 3	149	255.255.255.0	10.14.122.3	10.14.122.0
Sheet2

Excel Workbook
	A	B	C	D
1
2	Site	DC 3
3	Network Details	IP Address	Subnet Mask	Gateway
4	Primary IP	10.14.121.21	255.255.255.0	10.14.121.3
5	vLAN ID	147
6	Network	10.14.121.0
Sheet1

 

[kkrishna]

This working brilliantly. Thank you so much Peter ray:

 

[Peter_SSs]

This working brilliantly. Thank you so much Peter ray:

You are very welcome. Glad it helped.