# Concat function not working

Copper Contributor

# 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 - 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

# Re: Concat function not working

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)

# Re: Concat function not working

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

Thank you though.

# Re: Concat function not working

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

# Re: Concat function not working

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

# Re: Concat function not working

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!