Match and Get the Value from another table (Vlookup)

Question

I have 2 tables connected to each other through col A. I want to match the column C with Col A and get the value of Col B.

For Example,

Table 1

ColA    ColB     Colc
a        a1       b
a        b1       c
c        c1       a

Table2

ColA ColB
a     a1
b     b1
c     c1

Now, I have already created relationships between Table2 and Table1 for my other calculations connecting both the tables with the colA.

Now, I am trying to match ColC from Table1 with ColA of Table2 and return the value of ColB from Table2 as MatchedOutput.

Expected output Table1

 ColA    ColB     Colc     MatchedOutput
    a        a1       b     b1
    a        b1       c     c1
    c        c1       a     a1

The question's example would be much clearer for wider audience if the author had at least Colc in Table1 and ColA in Table2 containing values such as a2, b2, c2. Better still, all values in the two tables' columns (except the two columns that are used to link the tables) should be distinguishable so the expected output table could be understood at the first glance. — Nemo

Alexis Olson Alexis Olson · Accepted Answer · 2018-01-09T16:48:33

The DAX function for this is LOOKUPVALUE.

MatchedOutput = LOOKUPVALUE(Table2[ColB],Table2[ColA],Table1[ColC])

This looks for the value in Table2[ColB] where Table2[ColA] matches Table1[ColC].