Is there a way to do Data lookups with a form of Matrix using values in a X-axis and Y-axis of a grid to get a value.
Here is my basic use case on an onboarding form. Each business unit has a distribution group based off the city. so City would be on one axis and business unit on another with the group names.
I just cant figure out how to work conditions on the. I was thinking of using a Sharepoint list but maybe something else would be better?
New York | London | Paris | Tokyo | |
Business Unit 1 | DG-BU1-NY | DG-BU1-Lon | DG-BU1-Paris | DG-BU1-Tokyo |
Business Unit 2 | DG-BU2-NY | DG-BU2-Lon | DG-BU2-Paris | DG-BU2-Tokyo |
Business Unit 3 | DG-BU3-NY | DG-BU3-Lon | DG-BU3-Paris | DG-BU3-Tokyo |
Business Unit 4 | DG-BU4-NY | DG-BU4-Lon | DG-BU4-Paris | DG-BU4-Tokyo |
Alternately I was thinking of using two columns with conditions and the third the value but I think this would get very large fast with70+ cities and at least 10 business units to include.
BU | City | DG name |
Business Unit 1 | New York | DG-BU1-NY |
Business Unit 1 | London | DG-BU1-Lon |
Business Unit 1 | Paris | DG-BU1-Paris |
Business Unit 1 | Tokyo | DG-BU1-Tokyo |
Business Unit 2 | New York | DG-BU2-NY |
Business Unit 2 | London | DG-BU2-Lon |
Business Unit 2 | Paris | DG-BU2-Paris |
Business Unit 2 | Tokyo | DG-BU2-Tokyo |
Business Unit 3 | New York | DG-BU3-NY |
Business Unit 3 | London | DG-BU3-Lon |
Business Unit 3 | Paris | DG-BU3-Paris |
Business Unit 3 | Tokyo | DG-BU3-Tokyo |
Business Unit 4 | New York | DG-BU4-NY |
Business Unit 4 | London | DG-BU4-Lon |
Business Unit 4 | Paris | DG-BU4-Paris |
Business Unit 4 | Tokyo | DG-BU4-Tokyo |
I am hoping for en elegant solution but I guess I will take brute force.
We also have plans for more complicated lookups with Role calculation based on department, title and business unit.