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!!
Top comments (0)