Partial match when look up value is lengthier than corresponding value in a table or a range

[bhangale0905]

I have table as below. In first column name of the company and in next column corresponding manager Name.

Company Name​	Manager Name​
Amcor​	Amol​
Thermax​	Sagar​

The look up value of company name is not exact match in this case. e.g. It is "Thermax Ltd"
The company name from table is a subset of the look up value.
After applying formula, the out put should look like below:

Company Name​	Expected Output​
Thermax Ltd​	Sagar​
Amcor Ltd​	Amol​

Request your help folks.

Thanks,
Sagar

 

[aaleem]

in all cases if ltd is added then you can use the below formula
=VLOOKUP(LEFT(A2,LEN(A2)-4),$I$2:$J$3,2,FALSE)

 

[bhangale0905]

Thanks a ton Aaleem for your revert.

I think I need to modify my question.

I have table as follow (which is same as before)

Company Name​	Manager​
Amcor​	Amol​
Thermax​	Sagar​

The lookup value and the expected output to be as below:

Lookup Value​	Expected Output​
Thermax Ltd​	Sagar​
M/S Thermax Ltd​	Sagar​
Thermax Pvt Ltd​	Sagar​

Thanks & Regards

 

[aaleem]

It will be difficult if we do not have fixed pattern, however you can substitute certain text, you can modify my formula to suit your requirement.
=INDEX(A5:B10,MATCH(
TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D5,"Pvt",""),"M/s",""),"Ltd",""),"M/S",""),"m/s",""),"Pvt Ltd","")),A5:A10,0),2)

HTH