In this tutorial, you will learn how to use INDEX and MATCH to return multiple values in Excel.
For performing more complex lookups, Microsoft Excel’s INDEX and MATCH tools are the most frequently used. This is due to the versatility of INDEX and MATCH to conduct both transverse and longitudinal lookups. Excel’s INDEX and MATCH tools are combined in the INDEX MATCH function. Based on vertical and horizontal requirements, the two formulae merged may search up and produce the value of a cell in a database. In this piece, we’ll show you how to return multiple values to a cell using Excel’s INDEX MATCH feature.
Once you are ready, we can use real-life scenarios to help you understand how to use INDEX and MATCH to return multiple values in Excel.
Table of Contents
Use INDEX and MATCH to Return Multiple Values
Before we begin we will need a group of data to use INDEX and MATCH to return multiple values in Excel.
Step 1
First, you need to have a clean and tidy group of data to work with.
Step 2
In this example, we want to find the cities in ‘United State’ and return all the cities in it. To do that, we will insert the following formula =INDEX($C$1:$C$7, SMALL(IF(ISNUMBER(MATCH($A$1:$A$7,$E$2, 0)), MATCH(ROW($A$1:$A$7), ROW($A$1:$A$7)),””), ROWS($A$1:A1))).
Step 3
Once we are done, you will be able to use INDEX and MATCH to return multiple values. To return the rest of the cities, we can simply pull down the bottom-right corner of cell ‘F1’.
