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.
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.
Excel INDEX-MATCH Formula to Return Multiple Values Horizontally: 2 Easy Steps
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 function 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
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
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
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 with multiple criteria 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.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
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 give your suggestions in the comment box.
<< Go Back to INDEX MATCH | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
