Excel - ROW Function Problem - Expert Solution

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
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
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
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.

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.