Check Partial value , if match then leave otherwise change

[m_vishal_c]

Hi, i have two excel sheet in same workbook.

Sheet1 is having
[TABLE="width: 192"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 192"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Comapy Name[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]abc ltd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]def comms[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ghiuniversal pty ltd[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]jklquantas[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Sheet2 is having

[TABLE="width: 180"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Compnay[/TD]
[TD]value[/TD]
[/TR]
[TR]
[TD]abc[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]de[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]ghi[/TD]
[TD]34[/TD]
[/TR]
[TR]
[TD]jkl[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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

 

[Peter_SSs]

Based on that sample data, try this.

Excel 2016
	A	B
1	Compnay	value
2	abc	1
3	de	2
4	ghi	34
5	jkl	4
Sheet2

Excel 2016
	A	B
1	Comapy Name	Value
2	abc ltd	1
3	jklquantas	4
4	ghiuniversal pty ltd	34
5	def comms	2
6	hjjghjgh	#N/A
Sheet1

Cell Formulas
Range	Formula
B2	=LOOKUP(1,SEARCH(Sheet2!A$2:A$5,A2),Sheet2!B$2:B$5)

 

[m_vishal_c]

hi,
sorry to say that, this formula does not work.

 

[m_vishal_c]

hi,
sorry to say that, this formula does not work.

HI, I am extremely sorry, it works. i misunderstood . sorry for inconvenience

thanks for your support and time

 

[m_vishal_c]

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

 

[m_vishal_c]

hi Please check below link. i have uploaded this file. please let me know by send this file

https://abcvishalmasharuwala.sharefile.com/d-se82579d42694f39b

thanks

 

[jtakw]

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
	A	B	C	D
1	Company Name	LOOKUP	VLOOKUP	INDEX/MATCH
2	ABC	1	1	1
3	XYZ	2	2	2
4	MMM	4444	4444	4444
5	DD	4	4	4
Sheet1

Cell Formulas
Range	Formula
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
	A	B
1	Compnay	value
2	ABC AND CO PTY LTD	1
3	XYZ COMMISSION	2
4	MMM MARKETING	4444
5	DD COSTA	4
Sheet2

 

[m_vishal_c]

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.

	A	B	C	D
Company Name	LOOKUP	VLOOKUP	INDEX/MATCH
ABC
XYZ
MMM
DD

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]4444[/TD]
[TD="align: right"]4444[/TD]
[TD="align: right"]4444[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IFERROR(LOOKUP(1,SEARCH(A2,Sheet2!A$2:A$5),Sheet2!B$2:B$5),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=IFERROR(VLOOKUP("*"&A2&"*",Sheet2!A$2:B$5,2,0),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]=IFERROR(INDEX(Sheet2!B2:B5,MATCH("*"&A2&"*",Sheet2!A2:A5,0)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

	A	B

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]Compnay[/TD]
[TD="align: center"]value[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]ABC AND CO PTY LTD[/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]XYZ COMMISSION[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]MMM MARKETING[/TD]
[TD="align: center"]4444[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]DD COSTA[/TD]
[TD="align: center"]4[/TD]

</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

 

[jtakw]

Yes, did you try?

 

[m_vishal_c]

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