IF / VLOOKUP with partial text matching from a cell

K

kkrishna

New Member
Joined
Jan 12, 2011
Messages
16
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:

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Subnet[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD]VLAN[/TD]
[/TR]
[TR]
[TD]10.15.198.[/TD]
[TD]255.255.254.0[/TD]
[TD]10.15.198.1[/TD]
[TD]201[/TD]
[/TR]
[TR]
[TD]10.253.19.[/TD]
[TD]255.255.255.0[/TD]
[TD]10.253.19.1[/TD]
[TD]301[/TD]
[/TR]
</tbody>[/TABLE]






Your assistance on this regards greatly appreciate.

Thank you in advance.
 
Sort by date Sort by votes
Hi Peter,
Greetings!

After using this formula extensively, i've come across some hurdles due to IP lookup (first 3 octanes of B4) as there are multiple network address matching first 3 octanes but not last octane. So I was looking to modify the lookup value location instead IP. So instead of lookup B4 I would prefer to lookup B6 (which i'm going to make it manual entry similar to IP). So instead of lookup of part of cell content, now lookup complete cell value i.e. B6.

I'm going to drop off only this n/w address formula as i'm going to make it manual for lookup value.
=IFERROR(INDEX(Sheet2!F$3:F$1000,MATCH(SUBSTITUTE(LEFT(SUBSTITUTE($B$4,".","."&REPT(" ",9)),30)," ","")&$B$2,INDEX(Sheet2!$A$3:$A$1000&Sheet2!$B$3:$B$1000,0),0)),"Not found")

but below formula, I would like to updated lookup value with B6 replacing B4.
=IFERROR(INDEX(Sheet2!D$3:D$1000,MATCH(SUBSTITUTE(LEFT(SUBSTITUTE($B$4,".","."&REPT(" ",9)),30)," ","")&$B$2,INDEX(Sheet2!$A$3:$A$1000&Sheet2!$B$3:$B$1000,0),0)),"Not found")

would you mind to alter the formula based on B6 cell value keeping the rest of the formula intact and give me?

My sincere apologies in case any inconvenience caused

Br,
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
I'm not entirely clear.

1. You are going to enter B6 manually now?

2. What cells in Sheet1 do you want new formulas for?

3. What will Sheet2 look like now?

4. Could you give an example or two to demonstrate what you have and what result(s) you want?
 
Upvote 0
Hi!

Try this in Sheet1:

In C3 (Array Formula - use Ctrl+Shift+Enter to enter the formula) and copy to the right

=INDEX(Sheet2!$A$2:$F$5,
MATCH($B$5&$B$1,Sheet2!$F$2:$F$5&Sheet2!$B$2:$B$5,0),
MATCH(C$2,Sheet2!$A$1:$G$1,0))

In B4 (Array Formula - use Ctrl+Shift+Enter to enter the formula)

=INDEX(Sheet2!$A$2:$F$5,
MATCH($B$5&$B$1,Sheet2!$F$2:$F$5&Sheet2!$B$2:$B$5,0),
MATCH($A4,Sheet2!$A$1:$G$1,0))


[TABLE="class: grid, width: 1062"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Site[/TD]
[TD]DC 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Subnet[/TD]
[TD]Site[/TD]
[TD]vLAN ID[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD]Network Address[/TD]
[TD]Sheet2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Network Details[/TD]
[TD]IP Address[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]10.14.177.[/TD]
[TD]DC 2[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.14.177.1[/TD]
[TD]10.14.177.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Primary IP[/TD]
[TD][/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.121.3[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]10.14.27.[/TD]
[TD]DC 2[/TD]
[TD="align: right"]247[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.27.1[/TD]
[TD]10.14.27.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]vLAN ID[/TD]
[TD="align: right"]147[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]10.14.121.[/TD]
[TD]DC 3[/TD]
[TD="align: right"]147[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.121.3[/TD]
[TD]10.14.121.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Network[/TD]
[TD]10.14.121.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10.14.122.[/TD]
[TD]DC 3[/TD]
[TD="align: right"]149[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.122.3[/TD]
[TD]10.14.122.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***************[/TD]
[TD]***********[/TD]
[TD]*************[/TD]
[TD]**********[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]**********[/TD]
[TD]*****[/TD]
[TD]*********[/TD]
[TD]*************[/TD]
[TD]***********[/TD]
[TD]****************[/TD]
[TD]********[/TD]
[/TR]
</tbody>[/TABLE]


Markmzz
 
Last edited:
Upvote 0
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Site: [/TD]
[TD]Manual Entry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Network Address: [/TD]
[TD]Manual Entry[/TD]
[TD][/TD]
[TD][/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]Manual Entry[/TD]
[TD]Formula[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]vLAN ID: [/TD]
[TD]Formula[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
SHEET 1


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Subnet[/TD]
[TD]Site[/TD]
[TD]vLAN ID[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD]Network Address[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10.14.177.[/TD]
[TD]DC2[/TD]
[TD]246[/TD]
[TD]255.255.255.192[/TD]
[TD]10.14.177.1[/TD]
[TD]10.14.177.0[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]10.14.27.[/TD]
[TD]DC2[/TD]
[TD]247[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.27.1[/TD]
[TD]10.14.27.0[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]10.14.121.[/TD]
[TD]DC3[/TD]
[TD]147[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.121.3[/TD]
[TD]10.14.121.0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]10.14.122.[/TD]
[TD]DC3[/TD]
[TD]149[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.122.3[/TD]
[TD]10.14.122.0[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]10.16.89.[/TD]
[TD]DC3[/TD]
[TD]693[/TD]
[TD]255.255.255.192[/TD]
[TD]10.16.89.129[/TD]
[TD]10.16.89.128[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]10.16.89.[/TD]
[TD]DC3[/TD]
[TD]694[/TD]
[TD]255.255.255.192[/TD]
[TD]10.16.89.193[/TD]
[TD]10.16.89.192[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]10.16.88.[/TD]
[TD]DC2[/TD]
[TD]521[/TD]
[TD]255.255.255.192[/TD]
[TD]10.16.88.193[/TD]
[TD]10.16.88.192[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]10.16.88.[/TD]
[TD]DC2[/TD]
[TD]520[/TD]
[TD]255.255.255.192[/TD]
[TD]10.16.88.129[/TD]
[TD]10.16.88.128[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10.16.89.[/TD]
[TD]DC2[/TD]
[TD]522[/TD]
[TD]255.255.255.192[/TD]
[TD]10.16.89.1[/TD]
[TD]10.16.89.0[/TD]
[/TR]
</tbody>[/TABLE]
SHEET 2

1. You are going to enter B6 manually now? Yes. Since multiple networks are having same first 3 octanes (ex. row 8 & 9 from Sheet 2)
2. What cells in Sheet1 do you want new formulas for? C4, D4 & B5
3. What will Sheet2 look like now? No changes to sheet 2. Please refer above.
4. Could you give an example or two to demonstrate what you have and what result(s) you want? Hopefully you can understand by looking at the sheet 1 now?

I've moved previous B6 location to B2 in new sheet1.

Thank you,
Br
 
Upvote 0
Hi Markmzz, thanks for stepping in for assistance.
This seems to be wrong / non-functional formula. Wanted to share a screenshot but there is no option to upload. the values of C3 & B4 returns to #VALUE
 
Last edited:
Upvote 0
kkrishna said:
1. You are going to enter B6 manually now? Yes. Since multiple networks are having same first 3 octanes (ex. row 8 & 9 from Sheet 2)
2. What cells in Sheet1 do you want new formulas for? C4, D4 & B5
3. What will Sheet2 look like now? No changes to sheet 2. Please refer above.
4. Could you give an example or two to demonstrate what you have and what result(s) you want? Hopefully you can understand by looking at the sheet 1 now?

I've moved previous B6 location to B2 in new sheet1.

Thank you,
Br
Click to expand...

Hi!

First, you need to press Ctrl+Shift+Enter and not just Enter to enter the formula. If you don't, then you will have the error #VALUE!.

Now, try this (with the layout of your last post):

In C3 (Array Formula - use Ctrl+Shift+Enter to enter the formula) and copy to the right

=INDEX(Sheet2!$A$2:$F$10,
MATCH($B$1&$B$2,Sheet2!$B$2:$B$10&Sheet2!$F$2:$F$10,0),
MATCH(C$3,Sheet2!$A$1:$F$1,0))

In B4 (Array Formula - use Ctrl+Shift+Enter to enter the formula)

=INDEX(Sheet2!$A$2:$F$10,
MATCH($B$1&$B$2,Sheet2!$B$2:$B$10&Sheet2!$F$2:$F$10,0),
MATCH(SUBSTITUTE(A$5," :",""),Sheet2!$A$1:$F$1,0))


[TABLE="class: grid, width: 1062"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Site:[/TD]
[TD]DC3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Subnet[/TD]
[TD]Site[/TD]
[TD]vLAN ID[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD]Network Address[/TD]
[TD]Sheet2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Network Address:[/TD]
[TD]10.14.121.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]10.14.177.[/TD]
[TD]DC2[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.14.177.1[/TD]
[TD]10.14.177.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Network Details[/TD]
[TD]IP Address[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]10.14.27.[/TD]
[TD]DC2[/TD]
[TD="align: right"]247[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.27.1[/TD]
[TD]10.14.27.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Primary IP:[/TD]
[TD]Manual Entry[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.121.3[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]10.14.121.[/TD]
[TD]DC3[/TD]
[TD="align: right"]147[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.121.3[/TD]
[TD]10.14.121.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]vLAN ID :[/TD]
[TD="align: right"]147[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10.14.122.[/TD]
[TD]DC3[/TD]
[TD="align: right"]149[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.122.3[/TD]
[TD]10.14.122.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]10.16.89.[/TD]
[TD]DC3[/TD]
[TD="align: right"]693[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.89.129[/TD]
[TD]10.16.89.128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]10.16.89.[/TD]
[TD]DC3[/TD]
[TD="align: right"]694[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.89.193[/TD]
[TD]10.16.89.192[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]10.16.88.[/TD]
[TD]DC2[/TD]
[TD="align: right"]521[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.88.193[/TD]
[TD]10.16.88.192[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]10.16.88.[/TD]
[TD]DC2[/TD]
[TD="align: right"]520[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.88.129[/TD]
[TD]10.16.88.128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10.16.89.[/TD]
[TD]DC2[/TD]
[TD="align: right"]522[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.89.1[/TD]
[TD]10.16.89.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***************[/TD]
[TD]************[/TD]
[TD]*************[/TD]
[TD]**********[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]**********[/TD]
[TD]*****[/TD]
[TD]*********[/TD]
[TD]*************[/TD]
[TD]***********[/TD]
[TD]****************[/TD]
[TD]********[/TD]
[/TR]
</tbody>[/TABLE]


By the way, you can use the normal formula below too.

=INDEX(Sheet2!$A$2:$F$10,
MATCH($B$1&$B$2,INDEX(Sheet2!$B$2:$B$10&Sheet2!$F$2:$F$10,),0),
MATCH(C$3,Sheet2!$A$1:$F$1,0))

=INDEX(Sheet2!$A$2:$F$10,
MATCH($B$1&$B$2,INDEX(Sheet2!$B$2:$B$10&Sheet2!$F$2:$F$10,),0),
MATCH(SUBSTITUTE(A$5," :",""),Sheet2!$A$1:$F$1,0))

Markmzz
 
Last edited:
Upvote 0
markmzz said:
Hi!

First, you need to press Ctrl+Shift+Enter and not just Enter to enter the formula. If you don't, then you will have the error #VALUE!.

Now, try this (with the layout of your last post):

In C4 (Array Formula - use Ctrl+Shift+Enter to enter the formula) and copy to the right

=INDEX(Sheet2!$A$2:$F$10,
MATCH($B$1&$B$2,Sheet2!$B$2:$B$10&Sheet2!$F$2:$F$10,0),
MATCH(C$3,Sheet2!$A$1:$F$1,0))

In B5 (Array Formula - use Ctrl+Shift+Enter to enter the formula)

=INDEX(Sheet2!$A$2:$F$10,
MATCH($B$1&$B$2,Sheet2!$B$2:$B$10&Sheet2!$F$2:$F$10,0),
MATCH(SUBSTITUTE(A$5," :",""),Sheet2!$A$1:$F$1,0))


[TABLE="class: grid, width: 1062"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Site:[/TD]
[TD]DC3[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheet1[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]Subnet[/TD]
[TD]Site[/TD]
[TD]vLAN ID[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD]Network Address[/TD]
[TD]Sheet2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Network Address:[/TD]
[TD]10.14.121.0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]10.14.177.[/TD]
[TD]DC2[/TD]
[TD="align: right"]246[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.14.177.1[/TD]
[TD]10.14.177.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Network Details[/TD]
[TD]IP Address[/TD]
[TD]Subnet Mask[/TD]
[TD]Gateway[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]10.14.27.[/TD]
[TD]DC2[/TD]
[TD="align: right"]247[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.27.1[/TD]
[TD]10.14.27.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Primary IP:[/TD]
[TD]Manual Entry[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.121.3[/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]10.14.121.[/TD]
[TD]DC3[/TD]
[TD="align: right"]147[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.121.3[/TD]
[TD]10.14.121.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]vLAN ID :[/TD]
[TD="align: right"]147[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]10.14.122.[/TD]
[TD]DC3[/TD]
[TD="align: right"]149[/TD]
[TD]255.255.255.0[/TD]
[TD]10.14.122.3[/TD]
[TD]10.14.122.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]10.16.89.[/TD]
[TD]DC3[/TD]
[TD="align: right"]693[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.89.129[/TD]
[TD]10.16.89.128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7[/TD]
[TD]10.16.89.[/TD]
[TD]DC3[/TD]
[TD="align: right"]694[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.89.193[/TD]
[TD]10.16.89.192[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]8[/TD]
[TD]10.16.88.[/TD]
[TD]DC2[/TD]
[TD="align: right"]521[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.88.193[/TD]
[TD]10.16.88.192[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]10.16.88.[/TD]
[TD]DC2[/TD]
[TD="align: right"]520[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.88.129[/TD]
[TD]10.16.88.128[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10.16.89.[/TD]
[TD]DC2[/TD]
[TD="align: right"]522[/TD]
[TD="align: right"]2,55255E+11[/TD]
[TD]10.16.89.1[/TD]
[TD]10.16.89.0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]***[/TD]
[TD]***************[/TD]
[TD]************[/TD]
[TD]*************[/TD]
[TD]**********[/TD]
[TD]*******[/TD]
[TD]**[/TD]
[TD]***[/TD]
[TD]**********[/TD]
[TD]*****[/TD]
[TD]*********[/TD]
[TD]*************[/TD]
[TD]***********[/TD]
[TD]****************[/TD]
[TD]********[/TD]
[/TR]
</tbody>[/TABLE]


By the way, you can use the normal formulas below too.

=INDEX(Sheet2!$A$2:$F$10,
MATCH($B$1&$B$2,INDEX(Sheet2!$B$2:$B$10&Sheet2!$F$2:$F$10,),0),
MATCH(C$3,Sheet2!$A$1:$F$1,0))

=INDEX(Sheet2!$A$2:$F$10,
MATCH($B$1&$B$2,INDEX(Sheet2!$B$2:$B$10&Sheet2!$F$2:$F$10,),0),
MATCH(SUBSTITUTE(A$5," :",""),Sheet2!$A$1:$F$1,0))

Markmzz
Click to expand...

Sorry, typo (in red, the correct address).

Markmzz
 
Last edited:
Upvote 0
kkrishna said:
1. You are going to enter B6 manually now? Yes. Since multiple networks are having same first 3 octanes (ex. row 8 & 9 from Sheet 2)
2. What cells in Sheet1 do you want new formulas for? C4, D4 & B5
3. What will Sheet2 look like now? No changes to sheet 2. Please refer above.
4. Could you give an example or two to demonstrate what you have and what result(s) you want? Hopefully you can understand by looking at the sheet 1 now?

I've moved previous B6 location to B2 in new sheet1.
Click to expand...
If I have understood correctly, these would be the adjustments to my previous suggestions.

Excel Workbook
ABCDEF
1SubnetSitevLAN IDSubnet MaskGatewayNetwork Address
210.14.177.DC2246255.255.255.19210.14.177.110.14.177.0
310.14.27.DC2247255.255.255.010.14.27.110.14.27.0
410.14.121.DC3147255.255.255.010.14.121.310.14.121.0
510.14.122.DC3149255.255.255.010.14.122.310.14.122.0
610.16.89.DC3693255.255.255.19210.16.89.12910.16.89.128
710.16.89.DC3694255.255.255.19210.16.89.19310.16.89.192
810.16.88.DC2521255.255.255.19210.16.88.19310.16.88.192
910.16.88.DC2520255.255.255.19210.16.88.12910.16.88.128
1010.16.89.DC2522255.255.255.19210.16.89.110.16.89.0
Sheet2



Excel Workbook
ABCD
2Network10.14.121.0
3Network DetailsIP AddressSubnet MaskGateway
4Primary IP10.14.121.21255.255.255.010.14.121.3
5vLAN ID147
Sheet1
 
Upvote 0
Hi Markmzz, this seems working but just wanted to let you know that, a space in A5 of sheet1 is a mistake "vLAN ID :" it suppose to be "vLAN ID: " rest looks good.
 
Upvote 0
You must log in or register to reply here.

Similar threads

G
  • Solved
Searching a column for any partial matches from another cell text
Replies
4
Views
1K
Joe4
Joe4
A
  • Question Question
Vlookup formula to return specific text if cell contains match to list
Replies
3
Views
371
awoohaw
awoohaw
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
400
brianv
B
M
  • Question Question
I'm trying to search partial text in a range of cells and get back a corresponding cell to where the match was found.
Replies
2
Views
489
MrPickles79
M
E
  • Solved
Index/Match - Return value if partial text from cell contained anywhere in separate column
Replies
5
Views
4K
excelnewb221
E

Forum statistics

Threads
1,223,270
Messages
6,171,102
Members
452,379
Latest member
IainTru

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top