VLOOKUP won't let me select cells in another sheet

[Philman132]

Hi,

so I am trying to use VLOOKUP to fill in data from another workbook in a table of 20,000+ items. In the example below I have just copied the first 30 or so items into a seperate sheet to avoid images of thousands of numbers.

All I am trying to do is use the ENSG ID in the first file to lookup the gene name in the second file. Should be very simple, and I have done this many times before. but I have now run into a weird bug.

I start writing the formula as normal, as below, and select the ID that I want to look up: B2
Screenshot 2017-04-13 12.48.53.png

Then I switch to the second workbook and try and select the table, however excel doesn't register the change in workbook and just puts the plain reference into the formula without the workbook reference.
Screenshot 2017-04-13 12.49.48.png

Even weirder, when I finish off writing the full VLOOKUP forumula, it inputs the entire thing into whichever cell I happened to leave selected in the second sheet, rather than reverting back to the original sheet.
Screenshot 2017-04-13 12.50.04.png
Screenshot 2017-04-13 12.50.29.png

I have used the VLOOKUP command successfully many times before in similar situations, and I have no idea how to solve this problem. I don't know if I have changed some setting somewhere which has caused this to happen or what. Can anyone help?

[shukla.ankur281190]

Welcome to excel forum
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Philman132]

Ok I have uploaded two files I think.

The table I am tring to use VLOOKUP to get information TO is called isoform_fpkm_tableTest, the one I am trying to VLOOKUP data FROM is called fpkm_table_originalTest. There is a sheet2 inside the isoform table with an example of what I am trying to do. ( I wante to use VLOOKUP with more data afterwards, but this is just the gene names as they are not sensitive data)

[Glenn Kennedy]

I can't reproduce exactly what you've done. But it does work fine for me.

I did notice that the filename did not automatically appear in the formula in the 4th pic. How were you entering the A2 to B33 reference? Did you just type it in or did you select the cells directly from the source sheet?

on mine (both files open) it looks like this:



=VLOOKUP(B2,[fpkm_table_originalTest.xlsx]fpkm_table.txt!$A$2:$B$33,2,FALSE)

and with the source file closed, like this:
=VLOOKUP(B2,'C:\Users\Windows 8\Desktop\[fpkm_table_originalTest.xlsx]fpkm_table.txt'!$A$2:$B$33,2,FALSE)

[Philman132]

I did notice that the filename did not automatically appear in the formula in the 4th pic. How were you entering the A2 to B33 reference? Did you just type it in or did you select the cells directly from the source sheet?

Yes that is the problem, I usually go to the second file and highlight the table that I want to VLOOKUP from. Usually it inputs the filename and sheetname into the formula, but currently it just inputs the cell numbers and then relocates the entire formula to the new file.

I know what is SHOULD do, I just don't want to have to type out the filename and directory every time I do a vlookup! Previously it always inputted that into the formula automatically.

[avk]

refer attach file
In "C2"

=VLOOKUP(B2,'C:\Documents and Settings\Administrator\My Documents\Downloads\[fpkm_table_originalTest.xlsx]fpkm_table.txt'!$A$2:$B$33,2,0)

Also by index match you can do.
In "D2"

=INDEX('C:\Documents and Settings\Administrator\My Documents\Downloads\[fpkm_table_originalTest.xlsx]fpkm_table.txt'!$B$2:$B$33,MATCH(B2,'C:\Documents and Settings\Administrator\My Documents\Downloads\[fpkm_table_originalTest.xlsx]fpkm_table.txt'!$A$2:$A$33,0))

Copy paste down [Array formula ensure SHIFT+CTRL+ENTER]

[kev_]

This sounds like a system issue. Is this Excel getting its wires crossed?
Perhaps a full reboot first (including switching off the power)
Then try to recreate the problem with 2 new (simple) files.
(If the problem is still there - perhaps reinstall MS Office)
Then try the original 2 files again.

[Philman132]

This sounds like a system issue. Is this Excel getting its wires crossed?
Perhaps a full reboot first (including switching off the power)
Then try to recreate the problem with 2 new (simple) files.
(If the problem is still there - perhaps reinstall MS Office)
Then try the original 2 files again.

I restarted everything and created two new files as you suggested. The two new files did VLOOKUP normally between them absolutely fine. I reopened the old files again and still have the same problem. I tried doing a VLOOKUP between two other, completely unrelated, files that I also have in a completely different folder, and still have the same problem.

When I make a new file and save it, and try to do a VLOOKUP FROM an old file TO a new file, I have the problem, but if I try and do a VLOOKUP FROM the new file TO the old file, it works fine.

[kev_]

As I suspected - this tells us that the problem is an Excel system problem.
The lack of consistency (works properly with 2 clean files, not with old files) could point to a temporary file that is not auto-deleting.

I notice that you are on a MAC and so I think it is unlikely that anyone with windows can re-create this problem. I cannot see evidence on the internet of other users with the same problem.

I would try re-installing Office - that tends to be the quickest way to resolve problems like this.