Look up based on 2 seperate columns

[mdrew9]

Not sure if my title is the proper way to describe this.
I have a data table it has 2 columns, Activity and Element. I am trying to "look up" what this record is based on these values. This would be very simple if it was just activity, but its conditional first on element.
What I want to do is this
=IFERROR(VLOOKUP(E6,tblElement,2,FALSE),VLOOKUP(D6,tblActivity,2,FALSE)) In excel this works.

=IFERROR(related(tblElement[Color]),related(Activity[Color])) In Powerpivot this does not work because there is no error

I could also put this in Access as a query before bringing it into PP, so that is also an option.

Also typically in my pivot table I would just include the Color column as on of my fields, so I am calculated what each record is at each line. Not sure if there is a way around this.

 

[Laurent C]

Mavericks tblelement and tblactivity are not related they refer to 2 different types of codes.

Creating a relationship between your fact table and each of these columns should make your life easier.
YourFactTable ->tblActivity
YourFactTable -> tblElement