Excel - INDEX MATCH Function - Expert Solution

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.

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.