Hi all, I'm newer to Excel and I was wondering if any of you guys might have any insight for a problem that I'm having with my Vlookup function. I have two sheets and the identifier between them is the company name. One sheet (We can call that the Supplier List) has all the company names in rows with the associated street address and I am trying to pull a select few street addresses associated with these companies to the other spreadsheet (We can call that the Street Address List) so the Street Address List has the street addresses of some specific companies that I want. However, the names of the companies on the Supplier List have a number attatched to them like this: "MARKETO INC (1004118)", whereas the names of the companies on the Street Address List have the companies listed like this: "MARKETO INC". To trim the characters of the names of the companies on the Supplier List, I used this formula: =LEFT(B2563,LEN(B2563)-9), and removed the last 9 characters so that my VLOOKUP could have an identifier that matched. However, after running my VLOOKUP, only some of the street addresses that should have shown up show up, even though my identifiers match. Here is my VLOOKUP function: =VLOOKUP(C2, '[Identifier Copy of Supplier List.xlsx]sheet1'!$D:$X, 21, FALSE) Does anyone have any experience with this? Thanks for your help and let me know if you need any more information.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
V-Lookup Issues
- Thread starter nithchun
- Start date
dreid1011
Well-known Member
- Joined
- Jun 4, 2015
- Messages
- 3,633
- Office Version
- 365
- Platform
- Windows
It could be the space between the company name and the number.
The number, including the space, is 10 characters, but you are only removing 9 in your other formula.
"MARKETO INC (1004118)"
So, either change the formula to remove 10:
=LEFT(B2563,LEN(B2563)-10)
Or add TRIM to remove the extra space:
=TRIM(LEFT(B2563,LEN(B2563)-9))
The number, including the space, is 10 characters, but you are only removing 9 in your other formula.
"MARKETO INC (1004118)"
So, either change the formula to remove 10:
=LEFT(B2563,LEN(B2563)-10)
Or add TRIM to remove the extra space:
=TRIM(LEFT(B2563,LEN(B2563)-9))
Upvote
0
Solution
Try changing your LEFT formula to:
LEFT(B2563,LEN(B2563)-10)
In your example there is a space between the "C (" in MARKETO INC (1004118). You need to remove that space to have the company names match.
LEFT(B2563,LEN(B2563)-10)
In your example there is a space between the "C (" in MARKETO INC (1004118). You need to remove that space to have the company names match.
Upvote
0
