Excel - How to Use VLOOKUP and IF Function Together - Expert Solution

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

I am not sure if my vlookup is working correctly. I have 30,000 ID to match but only 3,000 matched which makes me concern. I need to identify the duplicates and eliminate them.
Solved by V. E. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 10/10/2018 - 04:33
Hello. Welcome to Excelchat, I see that your question is about VLOOKUP formula.
Excelchat Expert 10/10/2018 - 04:33
I can help you with that problem.
Excelchat Expert 10/10/2018 - 04:34
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.
Excelchat Expert 10/10/2018 - 04:34
Please tell me more on your requirement.
User 10/10/2018 - 04:35
Hi Im not sure how this works, is this the free trial?
Excelchat Expert 10/10/2018 - 04:35
Yes, if this is your first query, it is a free trial.
User 10/10/2018 - 04:35
Ok
Excelchat Expert 10/10/2018 - 04:35
Please tell me more on your requirement.
User 10/10/2018 - 04:36
I need to make sure the vlookup formula is correct
Excelchat Expert 10/10/2018 - 04:36
Ok
User 10/10/2018 - 04:36
Im afraid its not because only 3000 id matched out of 30,000 and I believe there should be more'
Excelchat Expert 10/10/2018 - 04:37
Ok.
User 10/10/2018 - 04:37
I'm trying to eliminate the duplicates
User 10/10/2018 - 04:37
through vlookup but my file is very large
User 10/10/2018 - 04:37
the time keeps clocking when I try to eliminate too
Excelchat Expert 10/10/2018 - 04:37
Please try and share the file.
Excelchat Expert 10/10/2018 - 04:37
You can attach the file or if you want please share the formula.
User 10/10/2018 - 04:38
Where do I download
User 10/10/2018 - 04:38
or attached
Excelchat Expert 10/10/2018 - 04:38
If we can eliminate the error just by the formula, I will.
Excelchat Expert 10/10/2018 - 04:38
Please use the the Pin icon just right to this chat window to attach the file
User 10/10/2018 - 04:39
My file is too large to attach
User 10/10/2018 - 04:39
=(
Excelchat Expert 10/10/2018 - 04:39
Ok. Please share the formula or a part of the file.
Excelchat Expert 10/10/2018 - 04:40
Or you can also paste the sample data on the right hand side preview.
Excelchat Expert 10/10/2018 - 04:41
You can also share the file via drop box and then sharing the download link here.
User 10/10/2018 - 04:42
not sure how to do drop box
Excelchat Expert 10/10/2018 - 04:42
Ok.
Excelchat Expert 10/10/2018 - 04:42
Please share the formula then.
User 10/10/2018 - 04:44
Sorry I hit something incorrectly
Excelchat Expert 10/10/2018 - 04:44
Ok.
Excelchat Expert 10/10/2018 - 04:45
We have time restriction per session. So I suggest you to share the formula or file so that I can help you within the assigned time.
Excelchat Expert 10/10/2018 - 04:47
Are you still there?
User 10/10/2018 - 04:48
Yep
Excelchat Expert 10/10/2018 - 04:48
Please respond.
User 10/10/2018 - 04:48
Im trying to load the info
Excelchat Expert 10/10/2018 - 04:48
Ok.
Excelchat Expert 10/10/2018 - 04:48
Can you just share the Vlookup here which you are using in the meantime you can build the info as required.
User 10/10/2018 - 04:50
Ok do you see this
Excelchat Expert 10/10/2018 - 04:50
Yes
User 10/10/2018 - 04:51
Is this formula correct?
Excelchat Expert 10/10/2018 - 04:51
The formula is correct but you need to make the range absolute.
User 10/10/2018 - 04:51
I have more data to add but its super slow because its too much
Excelchat Expert 10/10/2018 - 04:51
=vlookup(A2,Sheet2!A1:I54,1,false)
Excelchat Expert 10/10/2018 - 04:51
=vlookup(A3,Sheet2!A2:I55,1,false)
Excelchat Expert 10/10/2018 - 04:51
See the difference in both the formula
Excelchat Expert 10/10/2018 - 04:52
1st one is checking the data from A1 but the second is checking data from A2.
Excelchat Expert 10/10/2018 - 04:52
=vlookup(A2,Sheet2!$A$1:$I$54,1,false)
Excelchat Expert 10/10/2018 - 04:52
This should be your formula and then you copy it down.
Excelchat Expert 10/10/2018 - 04:53
So i have added $ to the range to make it absolute, so that when you copy the formula down, it will take the complete range for every search.
Excelchat Expert 10/10/2018 - 04:53
This is the reason you are getting less matches.
Excelchat Expert 10/10/2018 - 04:53
Does it make sense?
User 10/10/2018 - 04:54
Let me try it on my end
Excelchat Expert 10/10/2018 - 04:55
Sure.
User 10/10/2018 - 04:57
when you say absolute, should the table array be a specific column only?
Excelchat Expert 10/10/2018 - 04:58
Please copy and paste the exact formula you are using on your file.
Excelchat Expert 10/10/2018 - 04:58
Copy paste the first formula you have for the Vlookup here on chat.
User 10/10/2018 - 04:59
=VLOOKUP(A2,'Ramsey Cty Prim 2016'!Print_Area,1,FALSE)
Excelchat Expert 10/10/2018 - 04:59
so you have the range names as Print_Area
User 10/10/2018 - 05:00
ok, the second sheet is a print page
User 10/10/2018 - 05:00
how do I unformat that
User 10/10/2018 - 05:00
Im matching the vlookup onto the print page id
User 10/10/2018 - 05:01
is this an issue of print area?
Excelchat Expert 10/10/2018 - 05:01
Ramsey Cty Prim 2016 is the tab
User 10/10/2018 - 05:01
yep
Excelchat Expert 10/10/2018 - 05:01
Go to this tab
Excelchat Expert 10/10/2018 - 05:02
Then go to Formulas and Name Manager
Excelchat Expert 10/10/2018 - 05:03
You will see the "Print_Area" name listed there.
User 10/10/2018 - 05:03
the print area is the ramsey county tab
User 10/10/2018 - 05:03
so its included
Excelchat Expert 10/10/2018 - 05:03
I think if there is any error it is with the named range (if any)
Excelchat Expert 10/10/2018 - 05:04
Otherwise, I have to see the file to find the error.
User 10/10/2018 - 05:04
I see Printa Area in "Name Manager"
Excelchat Expert 10/10/2018 - 05:05
Ok. Select it and delete it. The again put the VLOOKUP formula using the absolute references as I explained above.
Excelchat Expert 10/10/2018 - 05:05
O
Excelchat Expert 10/10/2018 - 05:05
Or
Excelchat Expert 10/10/2018 - 05:05
If you select the name range, and click Edit - you will see the range it is for.
Excelchat Expert 10/10/2018 - 05:06
Please make sure it contains all the data.
Excelchat Expert 10/10/2018 - 05:07
Did it work?
User 10/10/2018 - 05:07
Trying it now
Excelchat Expert 10/10/2018 - 05:08
Ok
Excelchat Expert 10/10/2018 - 05:10
This is the last extension we have.
Excelchat Expert 10/10/2018 - 05:11
We will not be able to extend the time further than this.
User 10/10/2018 - 05:11
Yep I understand, I did get the frmula corrected. Thank you. I need to zero out all the NA
Excelchat Expert 10/10/2018 - 05:11
Great.
User 10/10/2018 - 05:12
I really appreciate thank
Excelchat Expert 10/10/2018 - 05:12
Does this solution solve your problem?
Excelchat Expert 10/10/2018 - 05:12
Do you have any other questions about this problem and the solution?
User 10/10/2018 - 05:12
Im not sure completely because my file is too large
Excelchat Expert 10/10/2018 - 05:12
Ok.
User 10/10/2018 - 05:13
Im not able to tell because of all the NA because eI can't calculate how many are being captured
Excelchat Expert 10/10/2018 - 05:13
Ok.
Excelchat Expert 10/10/2018 - 05:13
I hope you will not review me badly because of this.
Excelchat Expert 10/10/2018 - 05:14
If you could have shared the file, I would have solved it within few minutes.
Excelchat Expert 10/10/2018 - 05:14
You can share the file even with google drive.
Excelchat Expert 10/10/2018 - 05:14
I am still here to help and solve the issue.
Excelchat Expert 10/10/2018 - 05:15
You can count the number of N/A by using this formula
Excelchat Expert 10/10/2018 - 05:15
=COUNTIFS(L:L,"#N/A")
User 10/10/2018 - 05:15
No, I won't review you badly. I really appreciate our help. Should I download the file onto good drive?
Excelchat Expert 10/10/2018 - 05:16
just replace L:L with the column in which you have N/A like B:B OR C:c
User 10/10/2018 - 05:16
There I get is there's too much unique id that I can't capture all of them
Excelchat Expert 10/10/2018 - 05:16
Just upload it to google drive and then you can make it shareable and share the download link with me here on chat.
User 10/10/2018 - 05:16
ok
Excelchat Expert 10/10/2018 - 05:19
Do you need further help from my side?
User 10/10/2018 - 05:21
how do I download the file from googe drive
User 10/10/2018 - 05:21
new to this
Excelchat Expert 10/10/2018 - 05:21
Which file you want to download?
Excelchat Expert 10/10/2018 - 05:22
Have you uploaded the file to google drive?
User 10/10/2018 - 05:22
Yep
User 10/10/2018 - 05:22
I have
User 10/10/2018 - 05:22
the file is called Address
User 10/10/2018 - 05:22
Address Info
Excelchat Expert 10/10/2018 - 05:22
Now right click on the file you will see few options
User 10/10/2018 - 05:22
Can you see it?
User 10/10/2018 - 05:22
Ok
User 10/10/2018 - 05:22
Right click then
Excelchat Expert 10/10/2018 - 05:22
Click on get shareable link
Excelchat Expert 10/10/2018 - 05:23
it will generate the link, please share that link with me.
User 10/10/2018 - 05:23
https://drive.google.com/open?id=1NMvEOhEglzlMTJylzK0KpIcspaEO7l7G
Excelchat Expert 10/10/2018 - 05:24
ok
User 10/10/2018 - 05:24
Do you see the file
User 10/10/2018 - 05:24
and are you able to show me on here
Excelchat Expert 10/10/2018 - 05:24
I have the file.
Excelchat Expert 10/10/2018 - 05:24
Give me a minute
Excelchat Expert 10/10/2018 - 05:26
The file is too big to be open in google spreadsheet.
User 10/10/2018 - 05:27
Ok, I want to turn all the NA to 0 or blank so I can filter
Excelchat Expert 10/10/2018 - 05:27
Even my excel is hung because of the file.
User 10/10/2018 - 05:27
Been trying to find a solution to identify and them eliminate duplicates
User 10/10/2018 - 05:27
Im so sorry
Excelchat Expert 10/10/2018 - 05:27
You can use a if formual with Vlookup
User 10/10/2018 - 05:28
Ok what do Ineed to add?
Excelchat Expert 10/10/2018 - 05:30
please do this
Excelchat Expert 10/10/2018 - 05:30
IFERROR(YOUR VLOOK UP FORMULA , "")
Excelchat Expert 10/10/2018 - 05:30
IFERROR(FORMULA,"")
Excelchat Expert 10/10/2018 - 05:30
SO ALL N/A BECOMES BLANK CEE
Excelchat Expert 10/10/2018 - 05:30
CELL
User 10/10/2018 - 05:30
Ok thank you
Excelchat Expert 10/10/2018 - 05:31
i FOUND 10001 matching entries
User 10/10/2018 - 05:32
Ok that sound right
User 10/10/2018 - 05:32
Could you help resend the file to me
User 10/10/2018 - 05:32
I need to filter
Excelchat Expert 10/10/2018 - 05:32
Your formula is correct.
Excelchat Expert 10/10/2018 - 05:32
Just add the ifferror to your formula
Excelchat Expert 10/10/2018 - 05:33
=IFERROR(VLOOKUP(A2,'Ramsey Cty Prim 2016'!$A$1:$N$34282,1,FALSE),0)
Excelchat Expert 10/10/2018 - 05:33
iferror(formula,0)
Excelchat Expert 10/10/2018 - 05:33
then filter it without 0
Excelchat Expert 10/10/2018 - 05:33
thank you.

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.