johnywhy
New Member
- Joined
- Sep 12, 2008
- Messages
- 47
- Office Version
- 365
- 2019
- 2016
- 2013
- 2011
- 2010
- 2007
- Platform
- Windows
- MacOS
How to do a lookup against external data?
i'm not trying to pivot. It's just a lookup.
I have a Stores table on an Excel sheet. The Manager column is blank:
Stores (on worksheet):
Store_Number | City | Manager
1 | Rye |
2 | Rye |
3 | Derby |
4 | Napa |
There's an external data-source containing the Managers table:
Managers (external):
City | Manager
Rye | Joe Smith
Derby | Sue Jones
Napa | Lizzy Borden
How can i populate the Manager field in the Stores table from that external source? The name displayed in the Stores!Manager column should automatically update if user enters different city into City column.
I can't load that external source onto a sheet. Assume it's 10 million records. I think there are a few ways to connect to it from Excel:
- Get external data button
- New query button
- Connections
I think there are some possible solutions:
- create a relationship between the two tables
- data model
- a cell formula which does a lookup against the external data
- VBA UDF which executes SQL statement against the external data
- Power Query
- Power Pivot
But i'm not trying to pivot. It's just a lookup.
i like relationship or data-model join, because it's very intuitive and drag-drop.
Seems this scenario shouldn't require a UDF.
i can't figure out if a worksheet formula, like DGET, can use external datasource as the lookup table.
Also asked here
i'm not trying to pivot. It's just a lookup.
I have a Stores table on an Excel sheet. The Manager column is blank:
Stores (on worksheet):
Store_Number | City | Manager
1 | Rye |
2 | Rye |
3 | Derby |
4 | Napa |
There's an external data-source containing the Managers table:
Managers (external):
City | Manager
Rye | Joe Smith
Derby | Sue Jones
Napa | Lizzy Borden
How can i populate the Manager field in the Stores table from that external source? The name displayed in the Stores!Manager column should automatically update if user enters different city into City column.
I can't load that external source onto a sheet. Assume it's 10 million records. I think there are a few ways to connect to it from Excel:
- Get external data button
- New query button
- Connections
I think there are some possible solutions:
- create a relationship between the two tables
- data model
- a cell formula which does a lookup against the external data
- VBA UDF which executes SQL statement against the external data
- Power Query
- Power Pivot
But i'm not trying to pivot. It's just a lookup.
i like relationship or data-model join, because it's very intuitive and drag-drop.
Seems this scenario shouldn't require a UDF.
i can't figure out if a worksheet formula, like DGET, can use external datasource as the lookup table.
Also asked here
How to Lookup on External Data Source?
How to do a lookup against external data? I have a Stores table on an Excel sheet. The Manager column is blank: Stores (on worksheet): Store_Number | City | Manager 1 | Rye | 2 | Rye | 3 |...
www.excelforum.com