Inner join more than one column

[Arial12]

Hello there experts,

Two tables don't have a direct relationship.

Table 1 has more rows than table2:

Itemnumber, Code, description, priority

2                      001        Aztek         1

3                      003        valve          2

4                      010         Base          3

5                      010         Base          1

6                      004         BPL            2

10                    001         IVV             1

Table2:

Code, description, Priority, specialneed

001         Aztek         1                AB

003          Vakve        2                J

010           Base          1             AL

001            IVV            1              ZX

I want to add a column in table 1: Specialneed with a value from Table2.Specialneed where code, description and Priority matches from both table if there is no match then leave the value in Table1 blank for Specialneed column.

After the match Table1 expected:

Itemnumber, Code, description, priority  Specialneed

2                      001        Aztek         1               AB

3                      003        valve          2               J

4                      010         Base          3               

5                      010         Base          1                AL

6                      004         BPL            2

10                    001         IVV             1               ZX

I don't want to do another calculated table, just want to add a column in tabl1.

Any help is appreciated.

Thanks,

[TomMartens · Accepted solution]

Hey,

you can create a calculated column in Table1 by using the function LOOKUPVALUE like so:

SpecialNeed =
LOOKUPVALUE(
'Table2'[specialneed]
,'Table2'[Code],'Table1'[Code]
,'Table2'[description],'Table1'[description]
)

Hopefully this is what you are looking for.

Regards,

Tom