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.

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

**Read More:** Excel INDEX MATCH Example

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

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

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.

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

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

**🔎**** 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.

**Download Practice Workbook**

You can download the workbook and practice with them.

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

## Related Articles

- INDEX MATCH for Multiple Criteria in Rows and Columns in Excel
- How to Use INDIRECT, INDEX, and MATCH Functions in Excel
- How to Use INDEX MATCH with Multiple Criteria for Date Range
- Excel INDEX MATCH If Cell Contains Text
- How to Use INDEX MATCH Instead of VLOOKUP in Excel
- XLOOKUP vs INDEX-MATCH in Excel
- [Fixed!] INDEX MATCH Not Returning Correct Value in Excel
- INDEX, MATCH and MAX with Multiple Criteria in Excel

Hello, Can you use this same methodology to create a dropdown list? Didn’t seem to work when I tried.

Hello, MICHAEL!

https://www.exceldemy.com/excel-dependent-drop-down-list-multiple-words/

Please, check this article!

How do get the result to go across cells instead of down. Eg I would need the result to go into cells F8-G8 in the example. I have a spreadsheet with over 6000 rows

Hi ROBYN,

Thanks for your comment. I am replying to you on behalf of Exceldemy. To get the results across the cells, you need to drag the

Fill Handleto the right side and make the necessary changes if needed. Suppose, we also need thePricealong with the products. We can get the prices using some steps. Let me show you the process in the steps below.STEPS:1. Firstly, put the cursor on the bottom corner of

Cell F8. It will turn into a small plus sign.2. Now, drag the

Fill Handleto the right toCell G8.3. It will show the same result and formula because the

range D5:D12is locked.4. Now, select

Cell G8and go to theFormula Bar.5. Type

`$C$5:$C$12`

in place of`$D$5:$D$12`

becauseColumn Ccontains the prices.6. Press

Enter.7. After that, drag the

Fill Handledown.8. Finally, you will see the prices of the products.

To know more about copying formulas across cells you can check the article below.

Copy Formula Across CellsI hope this will help you to solve your problem. Please let us know if you have other queries.

Thanks!

Hello, Robyn! You can copy the formula into the cell where you need it.

Did you find a way?

Hi JESSICA,

Thanks for your comment. I am replying to you on behalf of Exceldemy. You can check the comment thread for the answer. Also, to know more about copying formulas across cells, you can check the link below.

Copy Formulas Across CellsThanks!

If you use COLUMNS instead of ROWS for the last function you can drag across multiple rows

Hi BEN,

Thanks for your comment. I am replying to you on behalf of Exceldemy. To know more about copying formulas across cells, you can check the link below.

Copy Formulas Across CellsThanks!