# 2 Steps to Concatenate Multiple Results with INDEX-MATCH in Excel

In this lesson, I’ll demonstrate detailed steps to INDEXMATCH 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.

Contents

## 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 INDEXMATCH Concatenate Multiple Results in Excel using different steps. To be exact, I’ve provided a total of 2 steps down below.

Step 1

### 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.

📕 Read More: 11 Easy Ways to Concatenate Rows in Excel

Step 2

### 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.

## 📄 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.

`📌`  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.