Normally, we can apply the VLOOKUP function to return the first matching values from the table cells. But, sometimes we need to extract all matching values and then separated them by a comma symbol. In this guide, we will show how to get and return the multiple matching values in a comma-separated list in Excel? Let’s get into this article!! Get an official version of ** MS Excel** from the following link: https://www.microsoft.com/en-in/microsoft-365/excel
General Formula:
- To get the multiple matches in Excel, you can use the below formula.
=TEXTJOIN(“, “,TRUE,IF(range1=criteria,range2,””))
Syntax Explanations:
- TEXTJOIN – This function will join values with a given delimiter separating each value. Read more on the TEXTJOIN function.
- TRUE – In Excel, this function will help to return the value TRUE if the given conditions will be TRUE or Vice Versa.
- IF – It helps to return one value for a TRUE result, and another for a FALSE result. Read more on the IF function.
- Comma symbol (,) – It is a separator that helps to separate a list of values.
- Parenthesis () – The main purpose of this symbol is to group the elements.
- Range -It represents the input range.
- Criteria – It is the specific value that wants to be found.
Example:
To find and return the matching values in a single cell, just follow the below steps:
- Refer to the below images. First, we will enter the input values in Column B and Column C.
- Here we are going to group the names based on the given criteria.
- Enter the above-given formula to the cell F3 or formula bar section.
- After applying the formula, Press CTRL + SHIFT + ENTER Keys.
- Finally, we will get the result in the selected cell as per the below image.
Verdict:
From this tutorial, you can get some knowledge about how to find the multiple matching values from a rang and return them into the comma-separated list in Excel. Hope you like it. Let me know your questions in the below comment sections. To learn more, check out Geek Excel!! And Excel Formulas!!
Discussion (0)