Description
Use this function to join together text strings from multiple cells based on a condition.
CONCATENATEIF is useful for merging data, generating summaries, and creating custom text outputs based on criteria.
Syntax
CONCATENATEIF(range, criteria, [delimiter], [concatenate_range])
Inputs
This function accepts the following arguments:
| Name | Required | Description | Valid input |
|---|---|---|---|
search_range |
Yes | The range of cells to be checked for the value in criteria. |
A cell reference, a cell range, or a formula which results in either of these. |
criteria |
Yes | The value that each cell in search_range must meet to be included in the concatenation. |
A text string, number, or a formula which results in either of these. |
delimiter |
No | The value to insert between concatenated values. | A text string, such as a comma, space, or any other supported character. |
concatenate_range |
No | The range whose cells match the rows that match criteria. |
A cell reference, a cell range, or a formula which results in either of these. |
Example
Sample data
A |
B |
|
|---|---|---|
1 |
1 |
Apple |
2 |
2 |
Banana |
3 |
3 |
Cherry |
4 |
4 |
Date |
5 |
5 |
Apricot |
Sample formulas
| Use case | Formula | Explanation and Result |
|---|---|---|
| Join together values from a range that match an exact value. | =CONCATENATEIF(B1:B5, "Apple", ", ") |
Combines every cell in B1:B5 that equals exactly "Apple," skipping "Apple Pie" since it isn't an exact match. Result: Apple |
| Join together values from a range that contain a specific letter, using a wildcard. | =CONCATENATEIF(B1:B5, "*a*", " | ") |
Combines every cell in B1:B5 that contains the letter "a" anywhere in it, skipping "Cherry" since it has no "a." Result: Apple | Banana | Date | Apricot |
| Join together values from a range that match an exact value, using a different delimiter. | =CONCATENATEIF(B1:B5, "Date", "|") |
Combines every cell in B1:B5 that equals exactly "Date." Result: Date |
| Look up a value from one range based on a match found in another range. | =CONCATENATEIF(B1:B5, "Date", "|", A1:A5) |
Finds the cell in B1:B5 that equals "Date," then returns the corresponding value from A1:A5 instead of the matched text itself. Result: 4 |
| Look up multiple values from one range based on a wildcard match found in another range. | =CONCATENATEIF(B1:B5, "*a*", " | ", A1:A5) |
Finds every cell in B1:B5 containing the letter "a," then returns the corresponding values from A1:A5, separated by a pipe. Result: 1 | 2 | 4 | 5 |
Notes
- If no cells meet the criteria, CONCATENATEIF returns an empty string.
- The delimiter is optional; if omitted, the values are concatenated without any separator.
- The range specified in
concatenate_rangemust be at least the size of the range supplied insearch_range. - CONCATENATEIF supports wildcards in the criteria, such as * for any number of characters and ? for a single character.
- CONCATENATEIF is case-insensitive.
Tips
- CONCATENATEIF can be combined with other functions such as IF and TEXT to create dynamic and flexible text outputs.
- Consider using CONCATENATEIF with large data sets to aggregate and analyze specific subsets of information.
- CONCATENATEIF is particularly useful for creating lists or strings from Sample Data that meet specific conditions.