Excel - How to Use a VLOOKUP Formula - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

hi can you help me out with the vlookup formula
Solved by A. D. in 22 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
User 28/08/2018 - 07:09
hello how are you?
Excelchat Expert 28/08/2018 - 07:09
Hello, welcome to got it pro.
Excelchat Expert 28/08/2018 - 07:09
I'm fine. Thanks for asking. How about you?
User 28/08/2018 - 07:09
[Uploaded an Excel file]
User 28/08/2018 - 07:09
thank you i am well too
Excelchat Expert 28/08/2018 - 07:09
I understand you need help with a vlookup formula.
User 28/08/2018 - 07:10
i'm sending you a file for reference
User 28/08/2018 - 07:10
yes please
Excelchat Expert 28/08/2018 - 07:10
I have the file opened on my PC.
User 28/08/2018 - 07:10
thank you
User 28/08/2018 - 07:10
here's the background....
Excelchat Expert 28/08/2018 - 07:10
Please tell me details about the problem.
Excelchat Expert 28/08/2018 - 07:10
I'm listening...
User 28/08/2018 - 07:10
the original file is the one under JACINTA
User 28/08/2018 - 07:10
and the new file is ADAM...
User 28/08/2018 - 07:11
i want to know who are the clients that still exist based on both files and who are the clients no longer there
User 28/08/2018 - 07:11
so basically just a comparison based on JACINTA's file
User 28/08/2018 - 07:11
so the two columns F & G
User 28/08/2018 - 07:12
i basically just need a comparison on both list
User 28/08/2018 - 07:12
makes sense?
User 28/08/2018 - 07:12
i'm not sure if i'm explaining clearly though
Excelchat Expert 28/08/2018 - 07:12
Totally makes sense. Although I'm not sure how to compare.
Excelchat Expert 28/08/2018 - 07:12
Are you saying that we should look at column C and D?
Excelchat Expert 28/08/2018 - 07:13
To compare which account is present on another account or not?
User 28/08/2018 - 07:13
yes....but the amounts sometimes wont match
User 28/08/2018 - 07:13
see go to line 3 and line 13
User 28/08/2018 - 07:13
there were changes for the naming
User 28/08/2018 - 07:13
in line 3 you will see "LUX"
User 28/08/2018 - 07:14
in 13 you will see "LUXEMBOURG"
User 28/08/2018 - 07:14
but same amount
User 28/08/2018 - 07:14
there were changes in the names in the new year that's' why it's a pain
User 28/08/2018 - 07:14
:(
Excelchat Expert 28/08/2018 - 07:14
Alright. In that case excel isn't much of a help. I'm really sorry to say that.
User 28/08/2018 - 07:14
and go to line 14 and 15
User 28/08/2018 - 07:15
i see...
User 28/08/2018 - 07:15
what do you suggest
User 28/08/2018 - 07:15
manual check?
Excelchat Expert 28/08/2018 - 07:15
Line 14 and 15 is pretty simple, they are a direct match. So, it is comparable but in the case of 3 and 13, excel recognize them as very different.
Excelchat Expert 28/08/2018 - 07:16
Yeah, manual check is the best way to go here.
Excelchat Expert 28/08/2018 - 07:16
Let me explain why.
Excelchat Expert 28/08/2018 - 07:18
You are saying LUX and LUXEMBOURG are the same. And this is only one instance. Now a rule can be written for this one instance. There might me numerous instances where another thing might be equal to one other thing. If you can tell me that these and these are equal, then I can also write rules for those.
User 28/08/2018 - 07:19
yes they are equal
User 28/08/2018 - 07:20
maybe write me a formula for that and i'll manually apply it to some other clients who i know are the same
Excelchat Expert 28/08/2018 - 07:20
The thing is you might forget to mention some match cases and there might also be some variations of the same case. For example, like 3 says " (LUX) SA LUX" where in line 13 says "LUXEMBOURG" only.
User 28/08/2018 - 07:20
ill do both manual and formula driven
User 28/08/2018 - 07:20
yeah then i'll manually change the data in the vlookup if that's the case
User 28/08/2018 - 07:21
can you give me one vlookup formula for lux then?
User 28/08/2018 - 07:21
i want to test it to this file
Excelchat Expert 28/08/2018 - 07:21
Ah, I'm sorry I should have said in clearly. When I said about writing a rule, I should have mentioned that there might not be a single formula.
User 28/08/2018 - 07:21
yeah that's fine
Excelchat Expert 28/08/2018 - 07:21
The formula would depend on case basis.
User 28/08/2018 - 07:21
just give me on single formula for the LUX client
User 28/08/2018 - 07:21
yes i know
User 28/08/2018 - 07:22
and i will manually amend it
Excelchat Expert 28/08/2018 - 07:23
Okay, first thing is 3 and 13 cannot be equal. There are additional strings in 3. For example, 3 has the texts "(LUX) SA LUX" and 13 has the text "BANK LUXEMBOURG".
Excelchat Expert 28/08/2018 - 07:24
if it was just LUX and "LUXEMBOURG" then it was possible.
Excelchat Expert 28/08/2018 - 07:24
I'm sorry I'm not able to explain why it is not possible more clearly.
Excelchat Expert 28/08/2018 - 07:28
I hope not to disappoint you but there is only so much we can do with excel. And this problem is such a thing that cannot be solved with excel formula.

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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.