How to Lookup on External Data Source?

johnywhy

New Member
Joined
Sep 12, 2008
Messages
47
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. 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
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
i tried this. This seems promising, but not working:

- add Stores sheet-table to data model
- open data model manager
- create join between Stores sheet table and external Managers table
- in data model, add new column to the sheet table. Assign it to DAX lookup formula on the external table

Code:
=LOOKUPVALUE(Managers[Manager],Managers[City],[City])

LOOKUPVALUE(<result_columnName>, <search_columnName>, <search_value>)

- The lookup works: I see correct Manager names next to each store in the data model manager
- close data model

Expected result:
- The Stores sheet should show the new lookup column created in the data model manager.

Actual result:
- The Stores sheet doesn't show the new lookup column created in the data model manager.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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
Back
Top