Hi All!
I am looking for a solution to the issue I have with vlookup formula working between two workbooks.
Let's assume there are 2 workbooks with data:
1) customer ID, customer name
2) customer ID, customer city
and I want to bring customer city from workbook 2 to the workbook 1 next to customer name.
So it's easy to put in third column in the workbook 1 appropriate vlookup formula, where lookup_value is customer ID.
And here is where the issue begins 🙂
Usually, when I am going from first workbook to the second, Excel 'keeps' vlookup formula in the formula bar expecting table_array. However, it happened to me many times, especially when I was working on large files, that when I was trying to select table_array in the second file, Excel was 'losing' the formula. I still could select some table_array but only within this workbook where I put the formula - not from the another workbook. It was like two workboooks that Excel couldn't connect through vlookup function. The solution that helped here was to close all excel workbooks ad open them again, but I'm wondering have You experienced such problem and have better solution? 🙂
What you're explaining hasn't ocurred to me except only if you have macro code that triggers an action like worksheet activate or deactivate or similar.
And of course this also applies to the second workbook if it's macro embedded
I am asuming that both workbooks are open
Hello, Thanks Hans for response
Yes two workbooks opened.
No macros inside, but thanks for this tip, it might be useful in other cases 😉
Hello,
I would use Power Query to combine the data, but only so if it is a recurring task.
There are lots of tips and guides in the blog section. If you want some help getting started just upload a sample file with some data and the expected result.
Br,
Anders
