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