Nashid
In this lesson, I’ll demonstrate detailed steps to INDEX–MATCH Concatenate Multiple Results in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.
📁 Download Excel File
The sample worksheet that was used during the discussion is available to download for free right here.
What is INDEX and MATCH Functions?
Excel’s INDEX and MATCH functions, when combined with one another, are used to execute complex lookups. The INDEX function returns a value from a particular cell within a range of cells, whereas the MATCH function returns the location of a value within a range of cells. Both functions return the same information. When used in conjunction with one another, these functions make it possible to seek up a value in a table based on a particular set of criteria.
For instance, you could use the INDEX and MATCH functions to locate the cost of a product based on its name, or the name of an individual based on their employee ID. Both of these searches may be carried out with the help of a database.
INDEX(range, row num, [column num])
“Range” – this part is the range of cells in which you wish to conduct a search.
“Row num” – it is the row number of the cell whose value you wish to return.
“Column num” – this is an optional argument that defines the column number from which the value should be returned. If this parameter is omitted, the method will return the specified row’s value in the range’s first column.
MATCH(lookup value, lookup range, [match type])
“Lookup value” – this is the value to look up in the specified range.
“Lookup range” – this part specifies the range of cells to search.
“Match type” – it is an optional input that determines how the lookup value should be matched by the function.It can be 0, 1, or -1, with 0 indicating “exact match,” 1 indicating “more than or equal to,” and -1 indicating “less than or equal to.” If you ignore this option, the method defaults to an exact match
Learn to Concatenate Multiple Results with INDEX-MATCH Using These 2 Steps
Here in this article, we will learn steps to INDEX–MATCH Concatenate Multiple Results in Excel using different steps. To be exact, I’ve provided a total of 2 steps down below.
Step 1: Determining Multiple Results Using INDEX and MATCH Functions
Here we will be using a formula using different functions to determine multiple results to concatenate later. Here we have a dataset of footballers from different teams. We want to find the players from one specific team. We are using INDEX, MATCH, SMALL, ISNUMBER, ROW, IF and ROWS functions to solve it. Follow the instructions below.
⬇️⬇️ STEPS ⬇️⬇️
- First, copy the following formula in a cell to get the search result.
=INDEX($C$6:$C$11, SMALL(IF(ISNUMBER(MATCH($B$6:$B$11,$F$6, 0)),MATCH(ROW($B$6:$B$11), ROW($B$6:$B$11)),""), ROWS($A$1:A1)))
🔨 Formula Breakdown
👉 ISNUMBER(MATCH($B$6:$B$11,$F$6, 0))
checks if the value in the range B6:B11 is found in F6, if so it returns true, otherwise it returns false.
👉 IF(ISNUMBER(MATCH($B$6:$B$11,$F$6, 0)),MATCH(ROW($B$6:$B$11), ROW($B$6:$B$11)),””)
The IF function checks if the result from the previous step is true, if it is true then it returns the position of the row in the range B6:B11 using the MATCH(ROW($B$6:$B$11), ROW($B$6:$B$11)) . If the result is false, it returns an empty string “”.
👉 SMALL(IF(ISNUMBER(MATCH($B$6:$B$11,$F$6, 0)),MATCH(ROW($B$6:$B$11), ROW($B$6:$B$11)),””), ROWS($A$1:A1))
The SMALL Function takes the array of the result from the IF Function and returns the nth smallest value, where n is the value in ROWS(A1:A1).
👉 INDEX($C$6:$C$11, SMALL(IF(ISNUMBER(MATCH($B$6:$B$11,$F$6, 0)),MATCH(ROW($B$6:$B$11), ROW($B$6:$B$11)),””), ROWS($A$1:A1)))
The INDEX function returns the value in the specified range (C6:C11) using the row number returned by the SMALL function as the row number and a fixed column number, in this case, the column number is 3.
So the formula returns multiple non-repeating results from column C based on the matching values of column B with F6 and returns the results in the order in which they appear in the data range.
- Now copy down the formula along the column after pressing Enter to get all the results.
📕 Read More: 11 Easy Ways to Concatenate Rows in Excel
Step 2: Concatenating Multiple Results
After determining the results, we are going to concatenate them now. For that, we are using the TEXTJOIN function. There are a lot of other ways and functions to concatenate multiple values or numbers like TEXT, CONCATENATE, Ampersand Sign “&” and CONCAT Function. Follow the steps below.
⬇️⬇️ STEPS ⬇️⬇️
- First, copy the following formula.
=TEXTJOIN("||",TRUE,B14:B16)
- Press Enter to get the Concatenated Result.
📕 Read More: 6 Ways to Concatenate with Carriage Return in Excel Formula
📄 Important Notes
🖊️ Use shortcuts to reduce the time, for instance, F4 for absolute value
🖊️ While writing formulas carefully, see the suggestions, and don’t forget the commas and parentheses.
🖊️ Be careful while defining criteria to be accurate as the Dataset.
🖊️ While copying any formula, try pasting it in the formula bar instead of the cell itself.
📝 Takeaways from This Article
📌 The article demonstrated the steps to INDEX-MATCH concatenate multiple results in Excel.
📌 In the first section, we demonstrated the syntax for INDEX and MATCH functions and briefly discussed them.
📌 After that, we showed how to use INDEX and MATCH functions to determine multiple results in an Excel dataset.
📌 Then, we demonstrated how to use the TEXTJOIN function to concatenate multiple results in Excel.
📌 Finally, we properly learned how to INDEX-MATCH concatenate multiple results step by step.
Conclusion
Firstly, I hope you were able to use the techniques I demonstrated in this INDEX–MATCH Concatenate Multiple Results in Excel lesson. As you can see, I’ve provided detailed steps on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly, I advise repeating the steps if you become confused in any of the steps if you get stuck. However, practice on your own after taking a look at the Excel file in the practice workbook I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel, spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles, please visit our website Excelden.com.
Related Articles
- 2 Ways to Bold Text in Concatenate Formula in Excel
- 5 Examples to CONCATENATE If Date Becomes Number in Excel
- 7 Ways to Concatenate Multiple Cells with Space in Excel
- 5 Easy Ways to Add Comma in Excel to Concatenate Cells
- 6 Ways to Concatenate Numbers with Leading Zeros in Excel
- 5 Easy Methods to Combine Rows into One Cell in Excel
