Check Partial value , if match then leave otherwise change

m_vishal_c

Board Regular
Joined
Dec 7, 2016
Messages
209
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Based on that sample data, try this.


Excel 2016
AB
1Compnayvalue
2abc1
3de2
4ghi34
5jkl4
Sheet2



Excel 2016
AB
1Comapy NameValue
2abc ltd1
3jklquantas4
4ghiuniversal pty ltd34
5def comms2
6hjjghjgh#N/A
Sheet1
Cell Formulas
RangeFormula
B2=LOOKUP(1,SEARCH(Sheet2!A$2:A$5,A2),Sheet2!B$2:B$5)
 
Upvote 0
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
 
Upvote 0
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.


Book1
ABCD
1Company NameLOOKUPVLOOKUPINDEX/MATCH
2ABC111
3XYZ222
4MMM444444444444
5DD444
Sheet1
Cell Formulas
RangeFormula
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)),"")



Book1
AB
1Compnayvalue
2ABC AND CO PTY LTD1
3XYZ COMMISSION2
4MMM MARKETING4444
5DD COSTA4
Sheet2
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

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