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'm trying to take a column with values and search a second sheet to identify if that value in the original column is present in the second column. I tried vlookup but it wants to change every column cell value dynamically instead of leaving the search range alone while dynamically adjusting the column number being searched. If I could just get it to say Yes or no that the value being searched is present in the second sheet, that would be enough
Solved by V. J. in 45 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 16/05/2018 - 07:05
Hi…Welcome to Got It Pro
User 16/05/2018 - 07:05
Hello, this is my first time trying this out
Excelchat Expert 16/05/2018 - 07:05
That's nice...I am here to help you.
Excelchat Expert 16/05/2018 - 07:06
Could you please share your file by using the pin-up here or illustrate it on this blank sheet?
User 16/05/2018 - 07:06
I'll illustrate it, the file is rather large
Excelchat Expert 16/05/2018 - 07:06
sure
User 16/05/2018 - 07:08
So, I have two sheets, sheet 1 has all the ID numbers for our sales, sheet 2 has a handful of those values
User 16/05/2018 - 07:08
I want to search sheet 2 for those values that are present in sheet 1
User 16/05/2018 - 07:08
I don't even need to pull back any information from sheet 2, just that it's present and I can figure out the rest from there
Excelchat Expert 16/05/2018 - 07:09
ok..got you
User 16/05/2018 - 07:09
and I want column B in sheet 1 to tell me if it's there, if I have to pull a value back, I would want the value in column B from sheet 2 to pull back, if the formula is void, I know it's not present, and if it pulls in a value, I know it's present in sheet 1
User 16/05/2018 - 07:10
but right now, when I try my vlookup, every time I copy the formula to the next row in column b, it's dynamically changing my lookup range
User 16/05/2018 - 07:10
I can post the formula i'm using now on my own sheet if you want to tell me what's wrong with the formula itself
Excelchat Expert 16/05/2018 - 07:11
Please place your formula in B1
User 16/05/2018 - 07:12
yup, it's uploaded, I'm saying A and B for example purposes, but the actual formula is trying to search D2 value on my original sheet (sheet1 in this case) and i'm pulling values from column B and C, trying to get column C's value to show up in sheet1 column B
User 16/05/2018 - 07:12
Ideally i'd like to be able to drag the formula down and have it pull d3, d4 etc, while keeping the range the same
Excelchat Expert 16/05/2018 - 07:18
I have it as Yes, if it finds the value in column A in Sheet2
Excelchat Expert 16/05/2018 - 07:19
now column C has the values from Column B of Sheet 2
Excelchat Expert 16/05/2018 - 07:20
Does that solve your query?
User 16/05/2018 - 07:20
I think so, I'm going to test both of them out on my sheet right now
Excelchat Expert 16/05/2018 - 07:21
sure
User 16/05/2018 - 07:23
hmm
User 16/05/2018 - 07:24
doesn't seem to be working, not sure what I've done wrong
Excelchat Expert 16/05/2018 - 07:24
Are both not working on your file?
User 16/05/2018 - 07:25
yeah, so i'm thinking there's something wrong with my original file, I'm checking to see if I left formulas instead of values in the columns
Excelchat Expert 16/05/2018 - 07:25
ok
User 16/05/2018 - 07:25
i'll try pasting in some of my actual lines in the document preview
Excelchat Expert 16/05/2018 - 07:25
That will be perfect
User 16/05/2018 - 07:27
just one moment, I accidentally lagged up my excel sheet
Excelchat Expert 16/05/2018 - 07:27
ok
User 16/05/2018 - 07:30
now that's odd...
User 16/05/2018 - 07:30
It pulled in a value even though It's not doing it on my own sheet
User 16/05/2018 - 07:30
oh nvm, that number isn't even on sheet 2
User 16/05/2018 - 07:31
anyway, my second sheet is actually called sheet1 on my own file
User 16/05/2018 - 07:31
that's why the formula is showing sheet1
Excelchat Expert 16/05/2018 - 07:32
yes, the sheet reference and cell reference has to be accurate to get the exact results
User 16/05/2018 - 07:33
they are correct on my own file, but it's not working correctly, not sure why. Wish I could upload the entire file but unfortunately I'm not allowed to disclose all of the info present
User 16/05/2018 - 07:33
some of the values are coming in, BUT oddly, they are all showing the exact same number in the vlookup
Excelchat Expert 16/05/2018 - 07:33
can you take a screenshot of both the sheet and upload
User 16/05/2018 - 07:35
oh! this might be important, the value they are pulling in for all the vlookups that are working are pulling the final value in column C
User 16/05/2018 - 07:36
yeah one moment, i'll try that
User 16/05/2018 - 07:41
I'm sorry, I'm terrible with Excel
User 16/05/2018 - 07:42
I'm just trying to figure out what I did wrong so I'm not giving you all the test formulas I'm trying
Excelchat Expert 16/05/2018 - 07:42
I can understand
User 16/05/2018 - 07:43
So i've got 2 sheets i'm trying to imitate them on, this one you can see the lookup number is in the E column instead of D
Excelchat Expert 16/05/2018 - 07:43
But without looking at the structure of your actual file, it difficult for me to troubleshoot
User 16/05/2018 - 07:43
yeah I understand
User 16/05/2018 - 07:44
let me see, it's not copying the full format and everything over when I paste them in, i'm trying to figure out how I can just grab a section out for you to look at
Excelchat Expert 16/05/2018 - 07:45
you can just copy a section and paste them as values in this google sheet. That way I can see the structure
Excelchat Expert 16/05/2018 - 07:46
I have moved the earlier workout to the right so that you can paste your structure in here on both sheets
User 16/05/2018 - 07:47
Honestly, I don't want to take up any more of your time, I have the formulas you gave me and I'm sure if I fiddle with it enough I can get those to work for me, can we mark this as solved and I'll let you get on to the next fellow who needs help =)
User 16/05/2018 - 07:48
I'm certain this is my own mistake, I think i'll try pulling all the values to an external sheet and try that way, I'm assuming there's some formula or format that's messing things up on my end
Excelchat Expert 16/05/2018 - 07:48
I can stay on if you want though as we have 16 more minutes.
User 16/05/2018 - 07:48
It's alright, I really appreciate your help, the formulas should be enough for me to figure out the rest with enough time. Thank you
Excelchat Expert 16/05/2018 - 07:49
Or else you can come back with a ready datset so that we can solve it
Excelchat Expert 16/05/2018 - 07:49
Please do give me a good feedback for my service.
User 16/05/2018 - 07:49
Yeah, if I keep having issues i'll be sure to come back
Excelchat Expert 16/05/2018 - 07:49
Thanks for your time. Please do come back for any new question. Have a great day ahead!
User 16/05/2018 - 07:49
I certainly will, you've been more than patient and helpful
User 16/05/2018 - 07:49
do I just hit end session then?
Excelchat Expert 16/05/2018 - 07:50
Yes, please.

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.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc