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
Lorenzo's avatar
Lorenzo
Silver Contributor
Apr 19, 2023

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

       

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Apr 19, 2023
      Glad you have it working now & Thanks for posting feedback
      Best...

Resources