Concat function not working

Copper Contributor

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 - THINLY SLICED SIRLOIN BEEF (1 STEAK))

 

I have done a formula for this but the output is TRUE instead of the concatenation scentence. My code is below:

=XLOOKUP(B3, XLOOKUP(A7, Products!A2:A9359, Products!C2:C9359) & "-" & XLOOKUP(A7, Products!A2:A9359, Products!D2:D9359) & "(" & XLOOKUP(A7,Products!A2:A9359, Products!I2:I9359) & ")",2, TRUE)

 

Any help is appreciated. Thank you.

5 Replies

Hi @Jadrian88 

 

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

 

Sample.png

 

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)

Thank you, I fixed it by removing the first XLOOKUP containing the B3 statement. It seems to work now.

Thank you though.
Glad you have it working now & Thanks for posting feedback
Best...

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

 

@Jadrian88 

Additional alternative approach…

the formula you provided is returning TRUE instead of the expected concatenated string.

One issue with your formula is that the second argument of the outer XLOOKUP function should be the lookup array, but it appears that you are using it to build the concatenated string. The third argument of the outer XLOOKUP function should be the return array, which is where you would specify the values to return based on the lookup value.

Here is an example of how you could use XLOOKUP and concatenation to build the desired string:

=XLOOKUP(A7, Products!A2:A9359, Products!C2:C9359) & " - " & XLOOKUP(A7, Products!A2:A9359, Products!D2:D9359) & " (" & XLOOKUP(A7,Products!A2:A9359, Products!I2:I9359) & “)”

This formula uses three separate XLOOKUP functions to find the brand, name, and product size of the product specified in cell A7. These values are then concatenated using the concatenation operator (&) and additional text to build the desired string.

 

I hope this helps!