Advanced exact partial match VLOOKUP help

ssigl

New Member
Joined
Jan 25, 2017
Messages
8
Hey Everyone

I really hope someone can help me with this!

I am doing a VLOOKUP match-up between company names in one column, and company names in another column. The company names have an inconsistent format (like HeroSwap, Inc. in one column and HeroSwap Incorporated in the other). I've cleaned out one column to now only consist of the company name (HeroSwap).

I am currently using this formula:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454545}</style>=IF(ISNA(VLOOKUP(K2&"*",U:U,1,FALSE)), " - ",VLOOKUP(K2&"*",U:U,1,FALSE))

What I'm looking to improve is to:



  1. Make sure that the clean company name only matches up with a full word in the other cell strings (right now HeroSwap will match with Heroswapper)
  2. Sometimes there are several different matches in the column I am using as my table_array. Is there anyway to show all matches in the string, separated by a comma or a similar solution?

Any expert help is greatly (!!!) appreciated.

Thanks very muchly
 

philaugust2004

Board Regular
Joined
Feb 1, 2017
Messages
192
can you post an example of what you have and what desired outcome is? That would make it easier to help
 

MUKESHY12390

Well-known Member
Joined
Sep 18, 2012
Messages
846
you don't you create "unique column" by combining two or three column.

eg. you have ComanyName, Region, Material, Owner

in this case ,take left(company,4digit)&region&material and then do lookup.


I'm trying say creating unique ID this way or other way would solve your problem.
 

ssigl

New Member
Joined
Jan 25, 2017
Messages
8
Okay, so an example could be this:

Column A has 10 company names:

Sabricota Inc.
Unified Corp.
Eternal Ltd.
Superfire, Inc.
MRWS LLC
Appdo Incorporated
Apricot, Inc.
Uturn S.A.
Riffle S.R.L.
UberCon Inc.

Column B has all the cleaned Company names, without the Inc., Ltd., LLC etc.
I use an individual cell as a lookup_value (Abricot)

When doing this with the formula i wrote in the description it will lookup the value in my chosen column, but it will return the first match it finds (in this case Sabricota, Inc. because it contains the word Abricot) and will leave out the one i really wanted (Abricot, Inc.)

What can i do to solve this problem? And possibly even show all of the matches?
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
... I am currently using this formula:

=IF(ISNA(VLOOKUP(K2&"*",U:U,1,FALSE)), " - ",VLOOKUP(K2&"*",U:U,1,FALSE))

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Helvetica Neue'; color: #454</style>What I'm looking to improve is to:

  1. Make sure that the clean company name only matches up with a full word in the other cell strings (right now HeroSwap will match with Heroswapper)...
Try the following array formula (to be entered using Ctrl+Shift+Enter, not just Enter):

=IFERROR(VLOOKUP(K2&" *",SUBSTITUTE(U2:U1000,","," ")&" ",1,FALSE), " - ")
 
Last edited:

ssigl

New Member
Joined
Jan 25, 2017
Messages
8
Unfortunately, this doesn't resolve the issue. It will still match Beli with Belitz & Garthoff. I want to make a condition that means it will only return matches of the lookup_value full company name (full string), and not to just find names that contain the lookup_value (like Beli is the beginning 4 letters in Belitz). I'd rather have this do a negative match.
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
Unfortunately, this doesn't resolve the issue. It will still match Beli with Belitz & Garthoff...
It does not in my test sheet -- because the search pattern is "Beli *" with a space between the letters and the asterisk.
Please make sure you have entered my formula exactly as shown in Post #5.
 

ssigl

New Member
Joined
Jan 25, 2017
Messages
8
Thanks, it works now. Sorry about that.

If anyone has a solution to number 2, please do tell :)

Thanks!
 

Forum statistics

Threads
1,081,690
Messages
5,360,614
Members
400,592
Latest member
badgergurl

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top