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 | 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:
https://www.mrexcel.com/board/thread...ource.1185564/

Hi,

load both data to datamodel and make a relation between them
then add a column to the big data and get the related manager


if its 10 mil as you say you will need a pivot so you can filter and make it visible in excel


note that a city can not have 2 managers, you will need to make the key this case the city more unique by maybe adding the store number to it and relate it that way

Thx for reply.

Originally Posted by Joske920

then add a column to the big data

I don't know what that means.

if its 10 mil as you say you will need a pivot so you can filter and make it visible in excel.

i think the purpose of a pivot isn't "cuz you have a lot of records", the purpose of a pivot is to show roll-ups. I'm not trying to show rollups, just lookups. The lookup is 10 mil, not the displayed table. Assume the Stores table is only 4 records.

a city can not have 2 managers

True -- my example data above doesn't tho.

thx

take a look at this example

maybe this gives you an idea