• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

xlookup of Concatenated Values


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.


  • ConcatQuestion.xlsx
    11.8 KB · Views: 2
Do you mean,
XLOOKUP([@Plant]&[@Material];Table1[Plant]&Table1[[Material ]];Table1[Value];"")


  • ConcatQuestion.xlsx
    12.5 KB · Views: 4
Because CONCAT(Table1[Plant];Table1[[Material ]]) will create a single string: "1002100310041005100610071008ABC123ABC124ABC125ABC126ABC127ABC128ABC129"

Whereas "&" creates an array of values:

that's why the second approach is working.