We can search match objects in Excel in different ways. There are different **Excel functions**, **VBA codes** are used to find the matching and return them. We will discuss how to find a match and return multiple values horizontally using the **INDEX** function.

**Table of Contents**hide

**Download Practice Workbook**

Download this practice workbook to exercise while you are reading this article.

## Introduction to Excel INDEX Function

**Objective:**

Returns a value of reference of the cell where a specific row intersects with another specified column in a given range.

**Syntax:**

**=INDEX(array, row_num, [column_num])**

**Or,**

**=INDEX(reference, row_num, [column_num], [area_num])**

**Arguments:**

**array-** Range of cells, columns, or rows considered for the values to look up.

**row_num-** Row position in the array.

**column_position-** Column position in the array.

**reference-** Range of arrays.

**area_num-** Serial number of an array in the reference, if you don’t mention it’ll consider as 1.

## An INDEX-MATCH Formula to Return Multiple Values Horizontally in Excel

We have the following dataset consisting of the name of cities with their corresponding countries.

Typically we use the combination of **INDEX **and **MATCH **functions as a replacement for **the LOOKUP function**. In this section, we will use this combination with the **COUNTIF **functions to return values horizontally.

**The MATCH function** returns the relative position of an item in an array that matches a specified value in a specified order.

**The COUNTIF function** counts the number of cells within a range that meet the given condition.

Now follow the steps below.

**Step 1:**

- We will find out the cities of
**CANADA**from the dataset. To do that, put the following formula in**Cell C15**.

`=IF(COLUMN()-2<=COUNTIF($B$5:$B$12,$C14), INDEX($C$5:$C$12,MATCH($C14,$B$5:$B$12,0)+COLUMN()-3),"")`

**Step 2:**

- Finally, click the
**Enter**button and drag the**Fill Handle**icon towards the right side.

As we can see, we’ve found **2** cities in **CANADA** from our dataset.

** **

**Code Explanation:**

**COLUMN()**Provides the column number.**Result: 3**

**MATCH($C14,$B$5:$B$12,0)**Search for a match of**Cell C14**from the**Range B5:B12**.**Result: 3**

**COUNTIF($B$5:$B$12,$C14)**Counts how many times**Cell C14**is found on the**Range B5:B12**.**Result: 3**

**INDEX($C$5:$C$12,MATCH($C14,$B$5:$B$12,0))**The**INDEX**operation is performed basis on the**MATCH**function result.**Result: Toronto**

**Read More:** **Multiple Criteria in Excel Using INDEX, MATCH, and COUNTIF Function**

**More Formula to Match & Return Multiple Values Horizontally**

**1. Combine INDEX with SMALL, IF, ROW, and COLUMN Functions**

In our first method, we will combine the **INDEX** function with **SMALL, IF, ROW,** and **COLUMN** to match and return values horizontally.

**The INDEX function** returns a value or reference of the cell at the intersection of a particular row and column, in a given range.

**The SMALL function** returns the **k-th** smallest value in a dataset.

**The IF function** checks whether a condition is met returns one value if **TRUE**, and another value is **FALSE**.

**The COLUMN function** returns the column number of a reference.

**The ROW function** returns the row number of a reference.

We want to find all the cities in the **USA **from the dataset and return their names horizontally. Now follow the steps below.

**Step 1:**

- We add two rows to the dataset. And put the
**USA**on**Cell C14,**which will be used as a reference for the formula.

**Step 2:**

- Go to
**Cell C15**and put the following formula.

`=INDEX($C$5:$C$12, SMALL(IF($C$14=$B$5:$B$12, ROW($B$5:$B$12)-ROW($B$5)+1), COLUMN(A1)))`

**Step 3:**

- Now, press the
**Enter**button to get the result.

**Step 4:**

- We want to get our results horizontally. So, pull the
**Fill Handle**icon towards the right side.

Here, we get the desired result through the horizontal axis.

**Code Explanation:**

**COLUMN(A1)**It will return the column number of**Cell A1**.**Result: 1**

**ROW($B$5)**It will return the row number of**Cell B5**.**Result: 5**

**ROW($B$5:$B$12)**It will provide an array of row numbers of**Range B5:B12**.**Result: {5, 6, 7, 8, 9, 10, 11, 12}**

**ROW($B$5:$B$12)-ROW($B$5)+1**A subtraction operation has been performed here, and**1 (one)**is added with each subtracted result. This result will be used as an argument for the**IF function**.**Result: {1, 2, 3, 4, 5, 6, 7, 8}**

**IF($C$14=$B$5:$B$12, ROW($B$5:$B$12)-ROW($B$5)+1)**The**IF function**matches the value of**Cell C14**with the**Range B5:B12**. Results**FALSE**when match not found.**Result: {1, FALSE, FALSE, FALSE, 5, 6, FALSE, 8}**

**SMALL(IF($C$14=$B$5:$B$12, ROW($B$5:$B$12)-ROW($B$5)+1), COLUMN(A1))**The**SMALL function**provides the smallest number from the array found by applying the**IF function**.**Result: 1**

**Read More:** **IF with INDEX-MATCH in Excel (3 Suitable Approaches)**

**Similar Readings**

**INDEX MATCH Multiple Criteria with Wildcard in Excel (A Complete Guide)****How to Use INDEX MATCH Formula in Excel (9 Examples)****Examples with INDEX-MATCH Formula in Excel (8 Approaches)****How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)****Excel INDEX MATCH with Multiple Criteria (4 Suitable Examples)**

**2. Incorporate INDEX and AGGREGATE Functions to Return Corresponding Values Horizontally**

In this section, we will combine the **INDEX **and **AGGREGATE functions** to find a match. The corresponding values will also return vertically in this way. We want to find out the name of the cities in **CANADA **by applying this formula.

**The COLUMNS function** returns the number of columns in an array or reference.

**The AGGREGATE function** returns an aggregate in a list or database.

Now execute the following steps.

**Step 1:**

- Write
**CANADA**at**Cell C14**.

**Step 2:**

- Paste the formula below on
**Cell C15**.

`=INDEX($C$5:$C$12,AGGREGATE(15,3,(($B$5:$B$12=$C$14)/($B$5:$B$12=$C$14)*ROW($B$5:$B$12))-ROW($B$4),COLUMNS($C$15:C15)))`

**Step 3:**

- Press the
**Enter**button and drag the**Fill Handle**icon towards the right side.

We get the **City **names vertically successfully.

**Code Explanation:**

**COLUMNS($C$15:C15)**It will return the number of columns from the given array.**Result: 1**

**ROW($B$4)**It will return the row number of**Cell B5**.**Result: 4**

**ROW($B$5:$B$12)**It will provide an array of row numbers of**Range B5:B12**.**Result: {5, 6, 7, 8, 9, 10, 11, 12}**

**AGGREGATE(15,3,(($B$5:$B$12=$C$14)/($B$5:$B$12=$C$14)*ROW($B$5:$B$12))-ROW($B$4),COLUMNS($C$15:C15))**In this section, the**AGGREGATE**function is used to perform a specific operation, which is defined by the**1st**argument of the**AGGREGATE**function. The**1st**argument is**15**and defines the**SMALL**function.**Result: 3**

**Read More:** **Index Function to Match and Return Multiple Values Vertically in Excel**

### 3. Insert TRANSPOSE-FILTER Formula for Horizontal Return of Multiple Values

In the above examples, we used the **INDEX **function to find a match so far. But here, we will show a method without using the **INDEX **function. Rather we will combine the **TRANSPOSE **and **FILTER **functions to find a match and return multiple values horizontally.

**The TRANSPOSE function** converts a vertical range of cells to a horizontal range and vice-versa.

**The FILTER function** filters a range or array.

For that, we will filter the values first, and then transpose that value to view horizontally.

**Step 1:**

- At first, move to
**Cell C15**and put the formula below.

`=TRANSPOSE(FILTER(C5:C12, C14=B5:B12))`

**Step 2:**

- Then press the
**Enter**button.

Finally, we get the city names here. Also, there is no need to drag the **Fill Handle** icon.

**Read More:** **Excel INDEX MATCH to Return Multiple Values in One Cell**

**Conclusion**

In this article, we discussed how to find a match object and return multiple values horizontally using the **INDEX-MATCH** formula. 3 more formulas were also shown with or without using the **INDEX **function. I hope this will satisfy your needs. Please have a look at our website **Exceldemy.com** and give your suggestions in the comment box.

## Related Articles

**Formula Using INDIRECT INDEX MATCH Functions in Excel****INDEX MATCH across Multiple Sheets in Excel (With Alternative)****Index Match Sum Multiple Rows in Excel (3 Ways)****Index Match Multiple Criteria in Rows and Columns in Excel****How to Match Multiple Criteria from Different Arrays in Excel****Index Match with Multiple Matches in Excel (5 Methods)****Excel Index Match single/multiple criteria with single/multiple results**