I have crosspost @ https://www.excelforum.com/excel-fo...vot-lookup-based-on-2-columns-conditions.html
I have 2 tables that does not have unique values. Given these, POWERPIVOT does not like it when I try to create a relationship as it's many to many.
How can I return a value (string as it's description) from the other table without relationship created and it's many to many?
Transaction Table
Lookup Table
Desired Result in Transaction Table
I have 2 tables that does not have unique values. Given these, POWERPIVOT does not like it when I try to create a relationship as it's many to many.
How can I return a value (string as it's description) from the other table without relationship created and it's many to many?
Transaction Table
GL | Functional Area |
1 | Z000 |
1 | Z001 |
1 | Z002 |
1 | Z003 |
1 | Z003 |
1 | Z004 |
2 | Z000 |
2 | Z001 |
2 | Z002 |
2 | Z003 |
2 | Z003 |
2 | Z004 |
Lookup Table
Group | GL | Functional Area |
Grocery Expense | 1 | Z000, Z001 |
Restaurant Expense | 1 | Z002 |
Entertainment Expense | 1 | Z003 |
Other Expense | 1 | Z004 |
Mortgage Expense | 2 | Z000, Z001 |
Utilities Expense | 2 | Z002 |
Interest Expense | 2 | Z003 |
Amortization/Depreciation | 2 | Z004 |
Desired Result in Transaction Table
GL | Functional Area | Group |
1 | Z000 | Grocery Expense |
1 | Z001 | Grocery Expense |
1 | Z002 | Restaurant Expense |
1 | Z003 | Entertainment Expense |
1 | Z003 | Entertainment Expense |
1 | Z004 | Other Expense |
2 | Z000 | Mortgage Expense |
2 | Z001 | Mortgage Expense |
2 | Z002 | Utilities Expense |
2 | Z003 | Interest Expense |
2 | Z003 | Interest Expense |
2 | Z004 | Amortization/Depreciation |