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

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.

index match concatenate multiple results


๐Ÿ“ 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

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.

using INDEX SMALL MATCH IF ROW ISNUMBER and ROWS functions to lookup multiple values in a range

Result of LOOKUP multiple values from a range using different functions

๐Ÿ“• 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)

Using the TEXTJOIN function to concatenate multiple results.

  • Press Enter to get the Concatenated Result.

concatenated results

๐Ÿ“• 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

(Visited 36 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo