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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]IP[/TD]
[TD]Subnet mask[/TD]
[TD]GateWay[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10.253.19.7[/TD]
[TD]=IF(SUBSTITUTE(A2,"10.253.19.","")<>A2,VLOOKUP(C2,Sheet2!A1:D3,2),"")[/TD]
[TD]=IF(SUBSTITUTE(A2,"10.253.19.","")<>A2,VLOOKUP(C2,Sheet2!A1:D3,3),"")[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this. Adjust ranges to suit your data.


Book1
ABCD
1SubnetSubnet MaskGatewayVLAN
210.15.198.255.255.254.010.15.198.1201
310.253.19.255.255.255.010.253.19.1301
Sheet2



Formula in B2 below is copied across and down.


Book1
ABC
1IPSubnet MaskGateway
210.253.19.7255.255.255.010.253.19.1
310.18.255.6Not foundNot found
410.15.198.200255.255.254.010.15.198.1
Sheet1
Cell Formulas
RangeFormula
B2=IFERROR(INDEX(Sheet2!B$2:B$1000,MATCH(SUBSTITUTE(LEFT(SUBSTITUTE($A2,".","."&REPT(" ",9)),30)," ",""),Sheet2!$A$2:$A$1000,0)),"Not found")
 
Upvote 0
[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.
 
Upvote 0
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?
 
Upvote 0
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.....
 
Upvote 0
See how this goes.

Excel Workbook
ABCDEF
1
2SubnetSitevLAN IDSubnet MaskGatewayNetwork Address
310.14.177.DC 2246255.255.255.19210.14.177.110.14.177.0
410.14.27.DC 2247255.255.255.010.14.27.110.14.27.0
510.14.121.DC 3147255.255.255.010.14.121.310.14.121.0
610.14.122.DC 3149255.255.255.010.14.122.310.14.122.0
Sheet2



Excel Workbook
ABCD
1
2SiteDC 3
3Network DetailsIP AddressSubnet MaskGateway
4Primary IP10.14.121.21255.255.255.010.14.121.3
5vLAN ID147
6Network10.14.121.0
Sheet1
 
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
375
awoohaw
awoohaw
A
  • Solved
Extract the require data from Sheet1 to Sheet2 (Total Consumption For an Item for a period in Sheet1 For Several Items) in another sheet2 get in deta
2
Replies
10
Views
220
DanteAmor
DanteAmor
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
407
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
497
MrPickles79
M

Forum statistics

Threads
1,223,893
Messages
6,175,246
Members
452,623
Latest member
cliftonhandyman

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