Forum Discussion
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.
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)
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!
