shumandoodah
New Member
Several times/day I need to look up concatenated values in another table. I usually resort to creating a concatenated column in the lookup table =CONCAT([@Plant],[@[Material ]]) in 'TABLE 1' and then other table my lookup is like 'TABLE 2' =XLOOKUP(CONCAT([@Plant],[@Material]),Table1[Concat],Table1[Value],"")
Table1 Table2
Plant Material Value Concat Plant Material Missing Value
1002 ABC123 10263 =CONCAT([@Plant],[@[Material ]]) 1002 ABC123 =XLOOKUP(CONCAT([@Plant],[@Material]),Table1[Concat],Table1[Value],"")
Is there a way to perform the lookup in the target table in a way that the concat column is unnecessary. I want to look up concatenated values, but I don't want to have to add another column for concatenated values. Example: =XLOOKUP(CONCAT([@Plant],[@Material]),CONCAT(Table1[Plant],Table1[[Material ]]),Table1[Value]',""). I hope that makes sense.
Table1 Table2
Plant Material Value Concat Plant Material Missing Value
1002 ABC123 10263 =CONCAT([@Plant],[@[Material ]]) 1002 ABC123 =XLOOKUP(CONCAT([@Plant],[@Material]),Table1[Concat],Table1[Value],"")
Is there a way to perform the lookup in the target table in a way that the concat column is unnecessary. I want to look up concatenated values, but I don't want to have to add another column for concatenated values. Example: =XLOOKUP(CONCAT([@Plant],[@Material]),CONCAT(Table1[Plant],Table1[[Material ]]),Table1[Value]',""). I hope that makes sense.