Get updated table from other workbook with formula(there are duplicates, so no data model/power query)

samztheman

New Member
Joined
Feb 15, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
I am trying to get information from a table in workbook 1, and have it dynamically update in another table on workbook 2.

I've tried everything, hours and hours No luck. Vlookup, index match, simple index. They keep returning huge errors.
Please help me wonderful community of Mr. Excel!
I can't use data models or power query as there are duplicates(no-no for data model), and my partners on this project have no excel knowledge at all and wouldn't be able to maintain the power query( I can barely get a handle on ensuring the data is clean and nulls are also no-no.) I need a simple answer, and I know its out there. So formula is how I want to go, but I've been failing miserably, and I know there is a crazy easy fix...
Index match with multiple conditions would get me close, but since there are multiple instances where the same person in a year on same project would visit multiple cities) (2015, Project 3, John Willmore visited two cities) index match just returns first City but not second. It also returns weird answers sometimes...
I need it to dynamically go down row.
The closest I got was was with this formula on Workbook 2 in the City Column:
=INDEX('Final Destination.xlsx'!Table1[#Data], 1, 5) which would return Oakland, but when I drag down, it just copies the same row 1, column 5, not row 6, column 5. I have thousands of entries, and I can't change each one. I want to make the formula easy to understand, so all my project partners can get it.
Any assistance would be appreciated.


Question.PNG

Capture.PNG
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,548
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top