Question description:
This user has given permission to use the problem statement for this
blog.
I am trying to perform a vlookup or index match for a large data set. Normally I rely on vlookup but it is giving me data from the wrong column whatever I do. I have tried to move to vlookup and it is doing exactly the same thing. Do you have any tips?
Solved by O. W. in 27 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
02/04/2018 - 03:36
hi there!
Excelchat Expert
02/04/2018 - 03:36
I'd love to help with your VLOOKUP issue
User
02/04/2018 - 03:37
Okay, how shall we proceed?
Excelchat Expert
02/04/2018 - 03:38
are you able to share your file? or - perhaps show me the syntax you're using with your formula and I can take a peek
User
02/04/2018 - 03:38
I don't have the file with me.
User
02/04/2018 - 03:39
I'm using the syntax just like all the video guides on youtube
User
02/04/2018 - 03:39
I think i should probably get back in touch with the spreadsheet in due course
Excelchat Expert
02/04/2018 - 03:40
so, the VLOOKUP is working, but it is returning the wrong value?
User
02/04/2018 - 03:40
Yes
User
02/04/2018 - 03:40
I'm reasonably experienced with Vlookup and have used it comfortably before incl. fixing issues relating to columns being in wrong positions
Excelchat Expert
02/04/2018 - 03:41
I'm sure you've already checked this, but the column reference in the VLOOKUP - is referencing the correct column number?
User
02/04/2018 - 03:41
yes 100%
Excelchat Expert
02/04/2018 - 03:41
yeah you definitely sound like you know the function!
User
02/04/2018 - 03:41
I read that Vlookup can make mistakes if columns have been added or something
Excelchat Expert
02/04/2018 - 03:41
hmmm i wish I could see the actual formula you've made...
User
02/04/2018 - 03:41
I use the template it gives you
Excelchat Expert
02/04/2018 - 03:41
definitely if the column is added within the lookup range..
User
02/04/2018 - 03:41
and lock the cells with f4
Excelchat Expert
02/04/2018 - 03:42
but if you fixed the reference column in the formula then that should've fixed it.
User
02/04/2018 - 03:42
As I said I think it's because there were columns added to the initial spreadheet
User
02/04/2018 - 03:42
basically i have a database
User
02/04/2018 - 03:43
i'll show you
User
02/04/2018 - 03:43
with an example
Excelchat Expert
02/04/2018 - 03:43
perfect
User
02/04/2018 - 03:47
So as Im showing you
User
02/04/2018 - 03:47
I have a database with patients who have had two operations
User
02/04/2018 - 03:47
one for each knee
User
02/04/2018 - 03:47
but we evaluate each knee post-op with the oxford score
Excelchat Expert
02/04/2018 - 03:47
gotcha
User
02/04/2018 - 03:48
so i needed to create a unique number for each data so that duplicates are not confused by the database
Excelchat Expert
02/04/2018 - 03:48
smart
User
02/04/2018 - 03:48
otherwise it will pull the oxford score for patient 1 left knee operation (row 2) when i want the right knee operation (row 4)
User
02/04/2018 - 03:49
so I set up in column one their unique ID which is 'patient number'&'date of operation'
User
02/04/2018 - 03:49
and i have every patient with a unique ID now
Excelchat Expert
02/04/2018 - 03:49
right since the patient #s are the same
Excelchat Expert
02/04/2018 - 03:49
cool very nice
User
02/04/2018 - 03:49
i put this into vlookup with everything correct as i've done a million times before
User
02/04/2018 - 03:50
and I ask it to extract column 6 the EQ5D data as shown here
User
02/04/2018 - 03:50
but for some reason it picks up a value like 7 columns along
User
02/04/2018 - 03:50
If you see I just added another column in F
User
02/04/2018 - 03:51
because initially there were duplicates of the patient number
User
02/04/2018 - 03:51
i've now removed those
User
02/04/2018 - 03:51
and it still isn't working
User
02/04/2018 - 03:51
same issue with index and match
User
02/04/2018 - 03:51
picks exactly the same column
Excelchat Expert
02/04/2018 - 03:52
so you have another table somewhere, where you're trying to populate the EQ5D 1Y value, and you have the unique ID in that other table, and the VLOOKUP is refrencing the original table (your example) - correct?
User
02/04/2018 - 03:52
so yes this is the 'table array' table
Excelchat Expert
02/04/2018 - 03:52
right
User
02/04/2018 - 03:52
the table I am trying to extract values from
User
02/04/2018 - 03:53
and the unique IDs are identical table to table
Excelchat Expert
02/04/2018 - 03:53
so it used to work like my example right?
User
02/04/2018 - 03:54
yes
Excelchat Expert
02/04/2018 - 03:54
i think the reference column should be 8... but you've tried changing it and it still brings back the same value from the reference table regardless of what number you enter in the formula?
Excelchat Expert
02/04/2018 - 03:55
is the vlookup being coded in the same worksheet/workbook?
User
02/04/2018 - 03:55
when i change the column number it does change the value
User
02/04/2018 - 03:55
but i can't seem to get it on the correct column whatever i do
Excelchat Expert
02/04/2018 - 03:55
so weird...
User
02/04/2018 - 03:55
the vlookup in the same file
Excelchat Expert
02/04/2018 - 03:55
are there hidden columns
Excelchat Expert
02/04/2018 - 03:55
ok good
User
02/04/2018 - 03:56
and the file has multiple tabs at the bottom
User
02/04/2018 - 03:56
i don't think i have any hidden columns
Excelchat Expert
02/04/2018 - 03:56
maybe try and select entire columns A-Z or something and right click, then unhide just to make sure
User
02/04/2018 - 03:57
okay
Excelchat Expert
02/04/2018 - 03:58
or i mean if you see column letters being skipped you know there's some hidden
User
02/04/2018 - 03:58
I don't believe that's the issue
Excelchat Expert
02/04/2018 - 03:58
ok.
User
02/04/2018 - 03:58
there are maximum 1 or 2 hidden columns
User
02/04/2018 - 03:58
the number difference between the target column and the column picked is maybe 5 or 6
Excelchat Expert
02/04/2018 - 03:59
because.. the hidden columns count as a column number when they are hidden
Excelchat Expert
02/04/2018 - 03:59
ok so when you change the reference column to a 2.. that should return the first column data in the array table, correct?
Excelchat Expert
02/04/2018 - 04:00
like my example
User
02/04/2018 - 04:00
it should
Excelchat Expert
02/04/2018 - 04:01
can you test? and then... are you able to change the 2 to a 3 and check to make sure returned correct data, then repeat with a 4, 5 etc. and watch it work properly. Then, once it stops working (not returning the right data) then you might uncover why it's not working... are you able to try that out?
User
02/04/2018 - 04:01
okay i shall do that tomorrow when i'm back at the office
User
02/04/2018 - 04:01
i'm going to register with you
Excelchat Expert
02/04/2018 - 04:02
i wish I could get you a better answer!
User
02/04/2018 - 04:02
is it always you that helps?
Excelchat Expert
02/04/2018 - 04:02
oh amazing...
User
02/04/2018 - 04:02
or are you a team?
Excelchat Expert
02/04/2018 - 04:02
unfortunately not.. but I am sure the next person will be able to help. It's really helpful that you're very knowledgeable with this stuff too... easy to work with.
User
02/04/2018 - 04:03
Alright thanks v much
Excelchat Expert
02/04/2018 - 04:03
im sure it's something small... just might be better if you can test in real time
Excelchat Expert
02/04/2018 - 04:03
apologies friend - hopefully I chat with you next time.
User
02/04/2018 - 04:03
yeah i couldn't access ur site on my work pc
Excelchat Expert
02/04/2018 - 04:03
gotcha
User
02/04/2018 - 04:03
annoying
Excelchat Expert
02/04/2018 - 04:03
totally!
User
02/04/2018 - 04:04
take care
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.
