Excel INDEX MATCH to Return Multiple Values in One Cell

The most often used functions in Microsoft Excel for executing more sophisticated lookups are INDEX and MATCH. This is because INDEX and MATCH are so versatile to perform transverse and longitudinal lookups. The INDEX MATCH function combines two Excel functions: INDEX and MATCH. The two formulae, when combined, may search up and bring the value of a cell in a database depending on vertical and horizontal requirements. In this article, we will demonstrate the process of how we can use the Excel INDEX MATCH to return multiple values in one cell.


Download Practice Workbook

You can download the workbook and practice with them.


Introduction to INDEX Function

The INDEX function is classified as a Lookup and References function in Excel.

  • Syntax

The syntax for the INDEX function is

INDEX(array, row_num, [column_num])

  • Arguments
ARGUMENTS REQUIREMENT EXPLANATION
array Required  This is an array element or a cell range.
row_num Required This is the row location from which a referral will return.
column_num Optional This is the column position from which a referral will be returned.
  • Return Value

Returns a value or references to a value from a table or range of values.


Introduction to MATCH Function

The MATCH function examines a cell for a particular match and returns its precise location within the range.

  • Syntax

The syntax for the MATCH function is

MATCH(lookup_value, lookup_array, [match_type])

  • Arguments
ARGUMENTS REQUIREMENT EXPLANATION
lookup_value Required This means that the value is in a range that will be checked.
 lookup_array Required This means the range within which the value will be searched.
match_type Optional Used to specify the function’s match type. In most cases, it is a numerical value. There are three sorts of matches that may be used:

To find an exact match, enter 0.

1 to discover the greatest value less than or equal to the search value.

-1 to discover the least value greater than or equal to the search value.

  • Return Value

Returns the value that represents a lookup array location.


Dataset Introduction

The INDEX function in Excel is extremely versatile and strong, and it appears in a large number of Excel calculations. The MATCH function is intended to locate the location of an element in a category.

To utilize the functions for returning multiple values into one cell, we are using the following dataset. The dataset represents a small local business that sells products after importing them from different countries. And, the dataset contains the Country in column B from where they import the products, the Price of each product in column C, and the Product name in column E.

Now, suppose, we need to extract all the products imported from a specific country.


Step-by-Step Procedures of Excel INDEX MATCH to Return Multiple Values in One Cell

Firstly, we can combine the lookup functions: INDEX MATCH to return multiple values. Together with these functions, we will need the SMALL, IF, and ISNUMBER functions.

The SMALL function produces a numeric value depending on its position in a list of the numeric value is categorized by value in increasing order. This function returns the minimum values from an array in a certain place.

The IF function performs a logical test and returns one value if the result is TRUE and another if the result is FALSE. This function compares two values and outputs any one of several results.

The ISNUMBER function checks not if a cell value is numeric. the ISNUMBER function shows TRUE when a cell includes a number; otherwise, it returns FALSE. ISNUMBER can be used to verify that a row represents a numeric value or that the output of some other function is a number. It accepts a single parameter, value, which can be a cell reference.


Step 1: Apply INDEX & MATCH Functions to Return Multiple Values

Assume that, first, we want to extract all the products imported from Australia using the INDEX MATCH function in this step. Let’s follow the procedures to use the function to return multiple values into one cell.

  • Firstly, select the cell where you want to put the formula.
  • Secondly, put the formula into that selected cell.
=INDEX($D$5:$D$12, SMALL(IF(ISNUMBER(MATCH($B$5:$B$12,$F$5, 0)), MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)),""), ROWS($A$1:A1)))
  • Further, press the Enter key to finish the procedure and see the result in that resulting cell.

Step-by-Step Procedures of Excel INDEX MATCH to Return Multiple Values in One Cell

  • After that, drag the Fill Handle down to duplicate the formula over the range. Or, to AutoFill the range, double-click on the Plus (+) symbol.

Step-by-Step Procedures of Excel INDEX MATCH to Return Multiple Values in One Cell

  • Finally, following above all sub-steps, we are able to see the result in cell range F8:F10.

Step-by-Step Procedures of Excel INDEX MATCH to Return Multiple Values in One Cell

🔎 How Does the Formula Work?

  • ROWS($A$1:A1): In this section, we use cell A1 as a starting point.
  • ROW($B$5:$B$12)): This part shows cells B5 through B12 are selected.
  • MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)),””): The portion looks for values that match exactly in the range (B5:B12) and returns them.
  • (MATCH($B$5:$B$12,$F$5, 0)): This section looks for values that match the value of cell F5 in the range (B5:B12).
  • ISNUMBER(MATCH($B$5:$B$12,$F$5, 0): Determines whether or not the matched values in the range (B5:B12) are numbers.
  • IF(ISNUMBER(MATCH($B$5:$B$12,$F$5, 0)): The line means that if there are any matching values in the range (B5:B12), the IF formula returns.
  • SMALL(IF(ISNUMBER(MATCH($B$5:$B$12,$F$5, 0)),MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)),””),ROWS($A$1:A1)): For each array, this function returns the lowest matching value.
  • INDEX($D$5:$D$12,SMALL(IF(ISNUMBER(MATCH($B$5:$B$12,$F$5, 0)),MATCH(ROW($B$5:$B$12), ROW($B$5:$B$12)),””),ROWS($A$1:A1))): Finally, this formula searches the array (D5:D12) for matched values and returns them in cell (F8:F10).

Step 2: Excel TEXTJOIN or CONCATENATE Function to Put Multiple Values in One Cell

Now, we need to combine the result into one single cell. For this purpose, we will use a different function. To do this we can either use the TEXTJOIN function or the CONCATENATE function. In this case, we will use them both in different steps. The TEXTJOIN function joins text from various ranges and/or characters, using a separator you define among each text value that will be joined. The CONCATENATE function in Excel is intended to connect multiple bits of text together or to summarize information from many cells into a single cell. For instance, let’s use the sub-procedures to use both functions to put the multiple-valued results into one cell.

  • In the first place, select the cell where you want to put the multiple-valued result into one cell.
  • Then, enter the formula into that cell.
=TEXTJOIN(", ",TRUE,F8:F10)
  • Finally, press Enter to see the result.

  • Instead of using the TEXTJOIN function, you can also use the CONCATENATE function in that selected cell. Likewise, the TEXTJOIN function, this function will work the same. So, enter the formula into that cell.
=CONCATENATE(F8,", ",F9,", ",F10)
  • Finally, similarly to before, press Enter key. As a result, this formula will show the result for putting the multiple values into one cell.


Conclusion

The above procedures will show you the procedures of Excel  INDEX MATCH to Return Multiple Values in One Cell. Hope this will help you! If you have any questions, suggestions, or feedback please let us know in the comment section. Or you can have a glance at our other articles in the ExcelDemy.com blog!

Sabrina Ayon

Sabrina Ayon

Hi there! This is Sabrina Ayon. I'm really excited to welcome you to my profile. Currently, I'm working in SOFTEKO as a Excel and VBA Content Developer. I'm a student of United International University and my program is Computer Science and Engineering. I love working with computers and solving problems. I’ve always been interested in research and development. So, here I will be posting articles related to Microsoft Excel. Hoped this may help you. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo