Forum Discussion
Formula to find and combine text
Does anyone know how to get excel to pull and combine text from different cells that meet specific requirements? I am needing to combine the text that has a specific value from column G and has a spe...
=TEXTJOIN(", ",,FILTER(A2:A16,(E2:E16=M2)*(G2:G16=M3)))
With a recent version of Excel you can apply this formula. The criteria for cols E and G can be entered in cells M2 and M3 and the formula dynamically updates the result.
=TEXTJOIN(",",,FILTER('DATA EXPORT'!Y:Y,('DATA EXPORT'!I:I=K5)*('DATA EXPORT'!F:F=B5)))
Here is what the formula I created based on your advice is. I am getting the #CALC! error? Any suggestions on how to fix that?
Here is what the formula I created based on your advice is. I am getting the #CALC! error? Any suggestions on how to fix that?
The most likely reason is that there is an empty array which means that there aren't any filter results.
How to correct a #CALC! error - Microsoft Support
=TEXTJOIN(",",,FILTER(Y:Y,(I:I=K5)*(F:F=B5)))
In my example the CALC! error is returned in cell AA13 because there isn't a match for GL code 102 and for description canteen in columns F and I.
- Is there a way to get the formula to return a blank cell or a N/A if there isn't anything that meets both requirements?
=IFERROR(TEXTJOIN(",",,FILTER(Y:Y,(I:I=K5)*(F:F=B5))),"")
You can wrap the formula into IFERROR like this in order to return a blank cell.