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:

[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.

 

[kkrishna]

Hi Peter_SSs, this is cool. awesome. working perfectly.

 

[Peter_SSs]

Hi Peter_SSs, this is cool. awesome. working perfectly.

Good news again!

 

[markmzz]

Hi Markmzz, how about adding "not found" instead #NA when there is no reference value found?

Ok. Try the formulas below (without space and #NA ):

=IFERROR(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)),"Not found")

=IFERROR(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($A5,":",""),Sheet2!$A$1:$F$1,0)),"Not found")

Markmzz