Forum Discussion

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

Lorenzo

Silver Contributor

Hi Jadrian88

Virtually impossible to suggest a fix with - only - the formula you posted. Structured a bit this gives:

XLOOKUP Syntax:

XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Assuming what's highlighted in blue "works", this represents the lookup_array of your 1st XLOOKUP. So your formula does XLOOKUP(B3, Blue_LookupArray, 2, TRUE) where 2 represents the return_array and TRUE the [if_not_found] argument

Hope this helps a bit. If you can't fix it yourself please upload (to OneDrive, Google Drive...) & share a representative workbook (without sensitive data)

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
Silver Contributor
Apr 19, 2023
Glad you have it working now & Thanks for posting feedback
Best...

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) & ")"
)