xlookup of Concatenated Values

[shumandoodah]

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.

 

[GraH - Guido]

Do you mean,

XLOOKUP([@Plant]&[@Material];Table1[Plant]&Table1[[Material ]];Table1[Value];"")

 

[shumandoodah]

OK, so it's permissible to use '&', but not concatenate in this instance. Interesting. Thanks!

 

[GraH - Guido]

Because CONCAT(Table1[Plant];Table1[[Material ]]) will create a single string: "1002100310041005100610071008ABC123ABC124ABC125ABC126ABC127ABC128ABC129"

Whereas "&" creates an array of values:
{"1002ABC123";"1003ABC124";"1004ABC125";"1005ABC126";"1006ABC127";"1007ABC128";"1008ABC129"}

that's why the second approach is working.