Find Part of a Text from a Different Column with VLOOKUP

[elaph]

Hi,


I have a list of companies in my sheet and in the other sheet, I have the Master List of Companies. I want to insert a formula to look the company's name from my Master sheet and result a data besides it. Attached sample for reference.

[shukla.ankur281190]

Try

C3

Please Login or Register  to view this content.

[elaph]

Thanks for the solution. I want to know how you made the Find formula to work this way. I just want to understand the concept.

=VLOOKUP(LEFT(B3,FIND(" ",B3)*2)&"*",Master!$B$2:$C$4,2,0) What does this formula do ?

[shukla.ankur281190]

I have used wild card character asterisk that is "*". I had watched in both sheet what world is making common in both sheet, i found the first word before first space is common, extracted the first word before space with help of LEFT function then added ampersand and asterisk too. When we add wildcard character, that does mean if any word starting is matching with my searched first value then after that whatever words come, there will be no problem those words would create.

Have a look at this web post https://exceljet.net/formula/partial-match-with-vlookup