Forum Discussion
Lookup values from multiple tables
Hi, I have the below Structure where "Table 3" is the main table connected to other 3 sources with details. I'd like to create a formula (calculated column) that will lookup the "Code" value from...
Greg_Deckler Thanks for your suggestion. However, the full data model is pretty big and I'd like to avoid any bi-directional relationships. I was thinking of a formula like :
Lookup Column = SWITCH(TRUE(),LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count])>1,LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count]),LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count])>1,LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count]),LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order])>1,LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order]),BLANK())It seems that it works fine, but I'm not sure if I'm taking into account all possible implications....
For your testing:
Table1Count Code 1 1234 2 1235 3 1236 Table 2
Count Code Date 2 1237 9/11/2020 5 1238 9/12/2020 6 1239 9/13/2020
Table 3Order Count user name Gender 1 2 432 AA M 2 4 433 AB F 3 5 434 AC M
Table 4
Thanks.Order Code Date status 1 1239 9/13/2020 Completed 2 1240 9/15/2020 Completed 4 1241 9/16/2020 Completed
Greg_Deckler Thanks for your suggestion. However, the full data model is pretty big and I'd like to avoid any bi-directional relationships. I was thinking of a formula like :
Lookup Column = SWITCH(
TRUE(),
LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count])>1,LOOKUPVALUE(Table1[Code],Tabel1[Count],Tabel3[Count]),
LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count])>1,LOOKUPVALUE(Table2[Code],Tabel2[Count],Tabel3[Count]),
LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order])>1,LOOKUPVALUE(Table4[Code],Tabel4[Order],Tabel3[Order]),
BLANK()
)
It seems that it works fine, but I'm not sure if I'm taking into account all possible implications....
For your testing:
Table1
For your testing:
Table1
| Count | Code |
| 1 | 1234 |
| 2 | 1235 |
| 3 | 1236 |
Table 2
| Count | Code | Date |
| 2 | 1237 | 9/11/2020 |
| 5 | 1238 | 9/12/2020 |
| 6 | 1239 | 9/13/2020 |
Table 3
| Order | Count | user | name | Gender |
| 1 | 2 | 432 | AA | M |
| 2 | 4 | 433 | AB | F |
| 3 | 5 | 434 | AC | M |
Table 4
| Order | Code | Date | status |
| 1 | 1239 | 9/13/2020 | Completed |
| 2 | 1240 | 9/15/2020 | Completed |
| 4 | 1241 | 9/16/2020 | Completed |
Anonymous You could use LOOKUPVALUE. How are your Table1 and Table3 related? What columns? And is it
Table1 1->* Table3
?
Greg_Deckler Table 1 is related to Table 3 as One to Many by Count columns. Thanks.