VLOOKUP not picking up table array in another spreadsheet

[steve_l]

I use VLOOKUP a lot and the simple VLOOKUP I am attempting is a very common exercise for me. That doesn't make me an expert just very familiar with it.

I have a worksheet where column A is a 5 digit reference and I use column A as the lookup value. In a worksheet in another spreadsheet I have a load of data but column A is also a 5 digit reference. I am trying to pull cell content from a single column of data from the second worksheet into the first worksheet when there is a match for the 5 digit reference.

Dead easy!

The problem is, using the wizard, the lookup value is entered ok but when I come to highlight the range of cells in the second worksheet nothing appears in the table array line in the wizard.

References in both worksheets are numerical and sorted in numerical ascending order. Both spreadsheets have been saved.

However if I copy the worksheet from the second spreadsheet and simply copy it into a new worksheet in the first and then try referencing it as a table array it works just fine.

For such a simple exercise I always use the wizard as it is so much quicker, but I can't figure out why it won't work the way it usually does.

Any ideas?

Thanks

Steve

 

[TerryHogarth21]

I believe you have 2 instances of Excel open at the same time. When you go to (On the Ribbon) View and Switch Window, do you have the name of both workbooks there? or just 1?

 

[muskiess]

Is it possible to do the vlookup where you have multiple instances open? I have two monitors and like to see them spread out side by side and use vlookup between the two workbooks, but it won't pull the table array from the other workbook.

 

[AntMac]

Try to do it without the wizard and just type in the formula selecting cells as you need. But I don't think you can do through two instances, you would need the two files open in one instance. Then after you've put in the formula you can open two instances if you like.

 

[AntMac]

I have tried and it seems once you go into the second instance, your leaving the first one. To your computer is no different them clicking from Excel then clicking power point. It's changing it's focus and holding the first instance where you were until you come back and resumes from there. That's the best way to explain what I've seen.

 

[muskiess]

Ok. I figured it would know that they are both excel files, but oh well. That would just make it easier, but if it can't be done, then what can you do? Thanks for your help.

 

[Sachin1983]

I believe you have 2 instances of Excel open at the same time. When you go to (On the Ribbon) View and Switch Window, do you have the name of both workbooks there? or just 1?

Hi Terry
I am facing the same issue. What is the solution to it? Please assist

 

[AntMac]

Now if you have Excel 2016 you can have multiple files open and they are all in one instance. So, this might be easier to do now. 1 improvement that gets an A rating. lol