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.
