Forum Discussion

Jadrian88's avatar
Jadrian88
Copper Contributor
Apr 19, 2023

Concat function not working

Hi, everyone the question states:   Use XLOOKUP and the concatenation operator to build the names of the products in the format Brand - Name (productsize) (eg 34183000045, ADVANCEPIERRE FOODS - T...
Excel
Excel on Mac
Formulas and Functions
Jadrian88's avatar
Jadrian88
Copper Contributor
Apr 19, 2023
Thank you, I fixed it by removing the first XLOOKUP containing the B3 statement. It seems to work now.

Thank you though.
Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 19, 2023

BTW Jadrian88 

 

Your formula is pretty inefficient as it does 3 times the same xlookup. Suggestion:

=LET(
  xlp, XLOOKUP(A7,Products!A2:A9359, Products!C2:I9359),
  flt, FILTER(xlp,{1,1,0,0,0,0,1}),
  TEXTJOIN("-",,INDEX(flt,,{1,2})) & "(" & INDEX(flt,,3) & ")"
)

can be shortened as:

=LET(
  flt, FILTER(XLOOKUP(A7,Products!A2:A9359, Products!C2:I9359),{1,1,0,0,0,0,1}),
  TEXTJOIN("-",,INDEX(flt,,{1,2})) & "(" & INDEX(flt,,3) & ")"
)

 

Resources