Question description:
This user has given permission to use the problem statement for this
blog.
hi, my vlookup wont work in some rows but does in others. its temperemental. e.g it will work on rows 10 to 15, then not row 16 and 17, but the will for the rest.
Solved by K. Q. in 21 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
User
01/10/2018 - 11:20
?
Excelchat Expert
01/10/2018 - 11:20
Hi, welcome to Got it Pro-Excel!
Excelchat Expert
01/10/2018 - 11:20
According to my diagnosis, you need help with vlookup formula, is that correct?
User
01/10/2018 - 11:20
yes
Excelchat Expert
01/10/2018 - 11:21
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. We also currently do not support VBA/Macro solutions.
User
01/10/2018 - 11:21
ok
Excelchat Expert
01/10/2018 - 11:21
Will you be able to paste some data in our shared sheet or send the file that you're working on?
User
01/10/2018 - 11:22
this is the formula
Excelchat Expert
01/10/2018 - 11:22
Can you send the file over so I can investigate it thoroughly?
User
01/10/2018 - 11:23
not really, its pretty confidential
Excelchat Expert
01/10/2018 - 11:23
No worries.
User
01/10/2018 - 11:23
this si what im seeying
Excelchat Expert
01/10/2018 - 11:23
Oh ok.
Excelchat Expert
01/10/2018 - 11:24
Let me ask you a couple of quick questions to make sure I fully understand your problem.
User
01/10/2018 - 11:24
and this is an example of the library
Excelchat Expert
01/10/2018 - 11:25
I'm looking at your formula and I can see that your value is a range.
Excelchat Expert
01/10/2018 - 11:25
What I mean by value is the value from the syntax.
Excelchat Expert
01/10/2018 - 11:25
=VLOOKUP (value, table, col_index, [range_lookup])
User
01/10/2018 - 11:25
yep
Excelchat Expert
01/10/2018 - 11:26
Is that the actual formula that you are using or just modified it as reference in here?
User
01/10/2018 - 11:26
thats the formula, obviously with an equal sign
User
01/10/2018 - 11:27
im not sure how else to do it
Excelchat Expert
01/10/2018 - 11:27
Ok, let me walk you through Vlookup formula.
Excelchat Expert
01/10/2018 - 11:27
This is the syntax of Vlookup
Excelchat Expert
01/10/2018 - 11:27
=VLOOKUP (value, table, col_index, [range_lookup])
Excelchat Expert
01/10/2018 - 11:27
I created a new sheet in our shared sheet for samples.
User
01/10/2018 - 11:28
ok cool
Excelchat Expert
01/10/2018 - 11:28
Because as I'm looking at your formula you got 50% of it.
User
01/10/2018 - 11:28
right
Excelchat Expert
01/10/2018 - 11:29
You already got table and col_index correctly.
User
01/10/2018 - 11:30
sweet
Excelchat Expert
01/10/2018 - 11:31
If you look at column F and G
Excelchat Expert
01/10/2018 - 11:31
That's your formula.
Excelchat Expert
01/10/2018 - 11:31
The value and range_lookup needs to be revised.
Excelchat Expert
01/10/2018 - 11:31
value is a specific cell or specific value and we don't use the actual range here.
Excelchat Expert
01/10/2018 - 11:32
Though your logic is on the right track, it just needs some revision.
User
01/10/2018 - 11:32
haha cool
Excelchat Expert
01/10/2018 - 11:32
Instead of G:G, you just need to reference G individually.
Excelchat Expert
01/10/2018 - 11:32
For example you start at row 2.
Excelchat Expert
01/10/2018 - 11:32
Reference G2.
Excelchat Expert
01/10/2018 - 11:33
And as you copy the formula below it it will adapt to G3, G4, etc. you don't need to change that individually.
Excelchat Expert
01/10/2018 - 11:33
Last will be your range_lookup
User
01/10/2018 - 11:33
but the library is 300 rows, and my reference section is only about 20
Excelchat Expert
01/10/2018 - 11:34
No worries, I can see that you used IFERROR function as well for that.
Excelchat Expert
01/10/2018 - 11:35
Let me explain the range_lookup first then you can try it from your end.
User
01/10/2018 - 11:35
sure
Excelchat Expert
01/10/2018 - 11:35
range_lookup is optional so you can leave that blank but the default value is TRUE. And you don't want TRUE which is approximate match.
Excelchat Expert
01/10/2018 - 11:35
So what we'll need to use is FALSE which is exact match.
User
01/10/2018 - 11:36
yep
Excelchat Expert
01/10/2018 - 11:36
You can also use 0 and 1
Excelchat Expert
01/10/2018 - 11:36
0=FALSE
Excelchat Expert
01/10/2018 - 11:36
1=TRUE
Excelchat Expert
01/10/2018 - 11:37
If you'll look at Sheet1
Excelchat Expert
01/10/2018 - 11:38
I applied a vlookup formula in Column B
User
01/10/2018 - 11:38
ooo
Excelchat Expert
01/10/2018 - 11:38
I love that "ooooh" reaction!
User
01/10/2018 - 11:38
hahahah
Excelchat Expert
01/10/2018 - 11:38
:)
Excelchat Expert
01/10/2018 - 11:39
Do you understand Vlookup function better than before?
User
01/10/2018 - 11:39
yes definite;y
Excelchat Expert
01/10/2018 - 11:39
That's great!
Excelchat Expert
01/10/2018 - 11:39
Do you think that will address your original question?
User
01/10/2018 - 11:40
yeah i think so, now i just exteded the time n didnt need it haha
Excelchat Expert
01/10/2018 - 11:40
No worries. You can end that from your end.
Excelchat Expert
01/10/2018 - 11:40
Would there be anything else I can assist you with regards to the original question and the solution provided?
User
01/10/2018 - 11:40
awesome, thank you so much
User
01/10/2018 - 11:40
nope all good!
Excelchat Expert
01/10/2018 - 11:40
If that's all, I'd like to wish you have a very nice day ahead of you and we'd love to hear from you again. I'd really appreciate if you leave a rating and comment at the end of this session. Thank you for using Got it Pro-Excel!
User
01/10/2018 - 11:40
ill be back or sure
Excelchat Expert
01/10/2018 - 11:41
You should see an end session button from your end. Warm regards!
Excelchat Expert
01/10/2018 - 11:41
That's great, thank you!
User
01/10/2018 - 11:41
same to 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.
