Looking for Alternative to Double VLOOKUP in PowerPivot

nikkollai

New Member
Joined
Sep 10, 2014
Messages
49
Hello,

So, i have two tables. Let's name the first one tbl_seaports it contains 2 columns POL and POD (port of loading and port of delivery). Basically it's two column list of from and to sea ports. Second table is the name and UN code of the sea port. I cannot figure out how to create a relationship and create the last table below showing POL POD and their relative codes (see last table)

Any ideas will be very helpful.

Here is the link for the tables: https://onedrive.live.com/redir?page=view&resid=3978FACCB9D92839!4902&authkey=!AJoItlp7zBd8PWk

tbl_seaports


POLPOD

<tbody>
</tbody>
BremerhavenSavannah
BremerhavenSeattle
BusanHouston
BusanLos Angeles
BusanOakland
BusanSeattle

<tbody>
</tbody>


tbl_LocationCode

Port NamePort Code
BremerhavenDEBRV
SeattleUSSEA
HoustonUSHOU
Los AngelesUSLAX
OaklandUSOAK
BusanKRPUS

<tbody>
</tbody>


RESULT I AM LOOKING FOR :

tbl_seaports

POLPODPOL Port CodePOD Port Code
BremerhavenSavannahDEBRVUSSSA
BremerhavenSeattleDEBRVUSSEA
BusanHoustonKRPUSUSHOU
BusanLos AngelesKRPUSUSLAX
BusanOaklandKRPUSUSOAK
BusanSeattleKRPUSUSSEA

<tbody>
</tbody>
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You can use the LOOKUPVALUE function:

POL Port Code:
Code:
=LOOKUPVALUE(tbl_LocationCode[Port Code],tbl_LocationCode[Port Name],[POL])

POD Port Code:
Code:
=LOOKUPVALUE(tbl_LocationCode[Port Code],tbl_LocationCode[Port Name],[POD])
 
Upvote 0
You could make some calculated columns like this in the SeaPortss table and no relationship needed for these. Will try and think of a measure for this as well.

=CALCULATE(VALUES(LocationCodes[Port Code]),FILTER(LocationCodes,LocationCodes[Port Name]=SeaPorts[POL from]))
=CALCULATE(VALUES(LocationCodes[Port Code]),FILTER(LocationCodes,LocationCodes[Port Name]=SeaPorts[POD to]))
 
Upvote 0

Forum statistics

Threads
1,213,550
Messages
6,114,265
Members
448,558
Latest member
aivin

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