Question description:
This user has given permission to use the problem statement for this
blog.
I can't make VLOOKUP work. I have 2 columns. Column A with a complete 11 digit NDC number, Column B contains a 9 digit NDC with the right 2 digits removed. I'm trying to populate column C with the full 11 digit number from column A based on a partial match with column B.
Solved by M. A. in 17 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
02/10/2018 - 12:05
Hello
Excelchat Expert
02/10/2018 - 12:05
Welcome to Excelchat, I see that your question is about Vlookup formula.
Excelchat Expert
02/10/2018 - 12:05
I can help you with that problem.
Excelchat Expert
02/10/2018 - 12:05
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
User
02/10/2018 - 12:06
Understood
Excelchat Expert
02/10/2018 - 12:06
Thank you.
Excelchat Expert
02/10/2018 - 12:06
Please tell me more on the problem you are facing or if you can build the sample data for me on the right or if you can share the file, it would be great.
User
02/10/2018 - 12:07
Column A is the full number - column B is a partial number with the two left most digits removed
Excelchat Expert
02/10/2018 - 12:08
Ok.
User
02/10/2018 - 12:08
I want to populate C or D with the full number from column A
Excelchat Expert
02/10/2018 - 12:08
Ok.
User
02/10/2018 - 12:08
Using a partial match from column B
Excelchat Expert
02/10/2018 - 12:09
Now that I understand your question, I’ll start working on it. Give me a couple of minutes to work on this.
User
02/10/2018 - 12:09
Thanks
Excelchat Expert
02/10/2018 - 12:09
Can I add an helper column, if required to?
User
02/10/2018 - 12:09
Sure
Excelchat Expert
02/10/2018 - 12:12
I’m working on your solution. But don’t worry, I’ll be updating you throughout!
User
02/10/2018 - 12:13
Got it - Thanks
Excelchat Expert
02/10/2018 - 12:15
Please check the result in Column D.
Excelchat Expert
02/10/2018 - 12:15
If this solves your problem I will explain the solution.
User
02/10/2018 - 12:15
That is the expected value
User
02/10/2018 - 12:15
It does
Excelchat Expert
02/10/2018 - 12:16
Ok. Let me explain the solution.
Excelchat Expert
02/10/2018 - 12:17
I added a helper column A
Excelchat Expert
02/10/2018 - 12:17
=value(left(B2,9))
Excelchat Expert
02/10/2018 - 12:18
Left(b2,9) will give the 9 characters from left on cell B2. Now this value is in form of text so using the value function I converted it to value.
User
02/10/2018 - 12:18
I see - you trimmed the full number to match the partial number.
Excelchat Expert
02/10/2018 - 12:18
Yes
Excelchat Expert
02/10/2018 - 12:18
and then using this VLOOKUP to get the result
Excelchat Expert
02/10/2018 - 12:19
=vlookup(C2,$A$2:$B$7,2,false)
User
02/10/2018 - 12:19
Would this work with numbers as well as text?
User
02/10/2018 - 12:19
Numeric values that is
Excelchat Expert
02/10/2018 - 12:20
if the values are all numerical it would work
Excelchat Expert
02/10/2018 - 12:20
that is the partial match required numbers
User
02/10/2018 - 12:22
That worked for me - well done
Excelchat Expert
02/10/2018 - 12:22
Great.
Excelchat Expert
02/10/2018 - 12:22
Do you have any other questions about this problem and the solution?
User
02/10/2018 - 12:22
None - I like this service -
Excelchat Expert
02/10/2018 - 12:22
Thanks for coming to Excelchat. Also, if you are satisfied with my solution, please go ahead and end the session using the button on the top right hand side of this chat window and requesting you to provide good feedback and review so that I can continue my services to others.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
