Check Partial value , if match then leave otherwise change

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
157
Hi, i have two excel sheet in same workbook.

Sheet1 is having
AB
Comapy NameValue
abc ltd
def comms
ghiuniversal pty ltd
jklquantas

<colgroup><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col></colgroup><tbody>
</tbody>


Sheet2 is having

AB
Compnayvalue
abc1
de2
ghi34
jkl4

<colgroup><col><col></colgroup><tbody>
</tbody>
I am going to apply vlookup on Sheet1 :- B3(abc ltd row) and get value from Sheet2 but it shows "#N/A"

Please advice me how can i get those value?

heaps in advance
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,253
Office Version
  1. 365
Platform
  1. Windows
Based on that sample data, try this.

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Compnay</td><td style="text-align: right;;">value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">abc</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">de</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">ghi</td><td style="text-align: right;;">34</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">jkl</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />

<b>Excel 2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Comapy Name</td><td style="text-align: right;;">Value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">abc ltd</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">jklquantas</td><td style="text-align: right;;">4</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">ghiuniversal pty ltd</td><td style="text-align: right;;">34</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">def comms</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">hjjghjgh</td><td style="text-align: right;;">#N/A</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=LOOKUP(<font color="Blue">1,SEARCH(<font color="Red">Sheet2!A$2:A$5,A2</font>),Sheet2!B$2:B$5</font>)</td></tr></tbody></table></td></tr></table><br />
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
157

ADVERTISEMENT

HI, I just have 1 question. in above code you applied range A2:A5 but what about nnumber of rows. what will be formula.
thanks
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146

ADVERTISEMENT

Hi,

For your uploaded sample, you need to "swap" the "Find Text", "Within Text" in the SEARCH function like below, I've also included VLOOKUP and INDEX/MATCH formula versions.
Also added IFERROR in case there's No match found.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Company Name</td><td style=";">LOOKUP</td><td style=";">VLOOKUP</td><td style=";">INDEX/MATCH</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">ABC</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td><td style="text-align: right;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">XYZ</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">MMM</td><td style="text-align: right;;">4444</td><td style="text-align: right;;">4444</td><td style="text-align: right;;">4444</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">DD</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td><td style="text-align: right;;">4</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IFERROR(<font color="Blue">LOOKUP(<font color="Red">1,SEARCH(<font color="Green">A2,Sheet2!A$2:A$5</font>),Sheet2!B$2:B$5</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=IFERROR(<font color="Blue">VLOOKUP(<font color="Red">"*"&A2&"*",Sheet2!A$2:B$5,2,0</font>),""</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">=IFERROR(<font color="Blue">INDEX(<font color="Red">Sheet2!B2:B5,MATCH(<font color="Green">"*"&A2&"*",Sheet2!A2:A5,0</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Compnay</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">value</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">ABC AND CO PTY LTD</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">XYZ COMMISSION</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">MMM MARKETING</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4444</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">DD COSTA</td><td style="text-align: center;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">4</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br />
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
157
Hi,

For your uploaded sample, you need to "swap" the "Find Text", "Within Text" in the SEARCH function like below, I've also included VLOOKUP and INDEX/MATCH formula versions.
Also added IFERROR in case there's No match found.

ABCD
1Company NameLOOKUPVLOOKUPINDEX/MATCH
2ABC111
3XYZ222
4MMM444444444444
5DD444

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

Worksheet Formulas
CellFormula
B2=IFERROR(LOOKUP(1,SEARCH(A2,Sheet2!A$2:A$5),Sheet2!B$2:B$5),"")
C2=IFERROR(VLOOKUP("*"&A2&"*",Sheet2!A$2:B$5,2,0),"")
D2=IFERROR(INDEX(Sheet2!B2:B5,MATCH("*"&A2&"*",Sheet2!A2:A5,0)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>



AB
1Compnayvalue
2ABC AND CO PTY LTD1
3XYZ COMMISSION2
4MMM MARKETING4444
5DD COSTA4

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet2

That's very very good. can this formula find something like "ABC'S AND CON PTY LTD". i just add " 's " in sheet2 and sheet 1 has ABC.

Please let me know. heaps thanks
 

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
157
Yes, did you try?


HI Applied below formula in sheet 1

=IFERROR(VLOOKUP("*"&A2&"*",Sheet2!A$2:B$5,2,0),"")


My sheet1 has "abc def" and sheet 2 has "abc's comms"

when i run that above formula it gives me Blank

Please guide me
thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,109,073
Messages
5,526,655
Members
409,713
Latest member
roman9980

This Week's Hot Topics

Top