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]

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

when i run that above formula it gives me Blank

That is because this is a different circumstance to what you had presented before. In all your earlier examples the whole of the column A text from one sheet appeared at the start of the column A text in the other sheet.
For example
ABC
and
ABC LTD

In your latest example that is not the case. How do we know what should match and what shouldn't? For example, do these match (after all, they do both start with "A")?
ABC LTD
and
AZZOPARDI SERVICES

 

[m_vishal_c]

That is because this is a different circumstance to what you had presented before. In all your earlier examples the whole of the column A text from one sheet appeared at the start of the column A text in the other sheet.
For example
ABC
and
ABC LTD

In your latest example that is not the case. How do we know what should match and what shouldn't? For example, do these match (after all, they do both start with "A")?
ABC LTD
and
AZZOPARDI SERVICES

hi, thanks for replying. i truly respect your concern. but in this case word will be same.
for example
ABC LTD
and
ABC'S PRIVATE LTD
thanks

 

[jtakw]

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

That actually is Not a match...what Other scenarios would there be? Please list All possible scenarios.

 

[m_vishal_c]

That actually is Not a match...what Other scenarios would there be? Please list All possible scenarios.

Hi, there are only 2 scenarios
(1) ABC LTD
and
ABC'S PRIVATE LTD
(2) A&B Ltd
and A & B ltd

Please guide me

thanks

 

[jtakw]

what Other scenarios would there be? Please list All possible scenarios.

Hi, there are only 2 scenarios
(1) ABC LTD
and
ABC'S PRIVATE LTD
(2) A&B Ltd
and A & B ltd

Use one of these updated formulas, the VLOOKUP and INDEX/MATCH has become Array formulas to be confirmed by CSE (Control, Shift, Enter)
The LOOKUP formula is normally entered.
Formulas copied down.

Book1
	A	B	C	D
1	Company Name	LOOKUP	VLOOKUP	INDEX/MATCH
2	ABC LTD	1	1	1
3	XYZ	2	2	2
4	MMM	4444	4444	4444
5	DD	4	4	4
6	A&B Ltd	9	9	9
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(LOOKUP(1,SEARCH(LEFT(A2,FIND(" ",A2&" ")-1),SUBSTITUTE(Sheet2!A$2:A$6," ","")),Sheet2!B$2:B$6),"")
C2	{=IFERROR(VLOOKUP("*"&LEFT(A2,FIND(" ",A2&" ")-1)&"*",SUBSTITUTE(Sheet2!A$2:B$6," ",""),2,0)+0,"")}
D2	{=IFERROR(INDEX(Sheet2!B$2:B$6,MATCH("*"&LEFT(A2,FIND(" ",A2&" ")-1)&"*",SUBSTITUTE(Sheet2!A$2:A$6," ",""),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Book1
	A	B
1	Compnay	value
2	ABC'S PRIVATE LTD	1
3	XYZ COMMISSION	2
4	MMM MARKETING	4444
5	DD COSTA	4
6	A & B ltd	9
Sheet2

 

[m_vishal_c]

Use one of these updated formulas, the VLOOKUP and INDEX/MATCH has become Array formulas to be confirmed by CSE (Control, Shift, Enter)
The LOOKUP formula is normally entered.
Formulas copied down.

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

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

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

[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/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(LEFT(A2,FIND(" ",A2&" ")-1),SUBSTITUTE(Sheet2!A$2:A$6," ","")),Sheet2!B$2:B$6),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array 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] "]C2[/TH]
[TD="align: left"]{=IFERROR(VLOOKUP("*"&LEFT(A2,FIND(" ",A2&" ")-1)&"*",SUBSTITUTE(Sheet2!A$2:B$6," ",""),2,0)+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!B$2:B$6,MATCH("*"&LEFT(A2,FIND(" ",A2&" ")-1)&"*",SUBSTITUTE(Sheet2!A$2:A$6," ",""),0)),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/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'S PRIVATE 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]

[TD="align: center"]6[/TD]
[TD="align: center"]A & B ltd[/TD]
[TD="align: center"]9[/TD]

</tbody>

Sheet2​

thanks a lot, it works perfectly. i have to apply above one of these formula on more than 4000 rows and it shows "Calculating (4 processor(s)) % " and file is stuck. it takes 3 to 4 mins to update entire file. and if i enter new data still it takes 3 to 4 mins to update. Is there any way i can avoid this much time. thanks

 

[jtakw]

Only advice I can give you is, try all 3 formulas and see which one works quickest for your file, and hope you're Not using Entire Column references (i.e. A:A, B:B, etc.), use a limited range larger than you need.

 

[m_vishal_c]

Only advice I can give you is, try all 3 formulas and see which one works quickest for your file, and hope you're Not using Entire Column references (i.e. A:A, B:B, etc.), use a limited range larger than you need.

Hi, yes you are right. i was using entire column (i.e. A:A, B:B, etc.). it works good now without using entire column. but i have to change formula every time when i enter new data. is there any way that i do not need to change formula or this formula get total rows number in formula automatically. i do not need to change formula that's why i used (i.e. A:A, B:B, etc.). please guide me. thanks

 

[Peter_SSs]

Just make the ranges big enough to cover any amount of data you are likely to have. You mentioned ab out 4,000 rows, so perhaps make the range 5,000 rows? .. or 10,000 if you think you might need that. Either way it would be much better than 1,000,000+ rows.

 

[m_vishal_c]

Just make the ranges big enough to cover any amount of data you are likely to have. You mentioned ab out 4,000 rows, so perhaps make the range 5,000 rows? .. or 10,000 if you think you might need that. Either way it would be much better than 1,000,000+ rows.

Ok, got it. thanks for update me. much appreciate