How to Return Multiple Values in Google Sheets Based on One Criteria

You can use the following formula to return multiple values in Google Sheets based on a single criteria:

=INDEX($A$1:$A$14, SMALL(IF(D$2=$B$1:$B$14, MATCH(ROW($B$1:$B$14), ROW($B$1:$B$14)), ""), ROWS($A$1:A1)))

This particular formula is designed to return all of the values located within the data range A1:A14, specifically where the corresponding value in the criteria range B1:B14 is exactly equal to the single criteria value specified in cell D2. Understanding how each nested function contributes to this result is key to successful implementation and debugging.

Deconstructing the Complex INDEX/SMALL/IF Formula

To effectively utilize this solution, we must break down the purpose of each function within the nested structure. This intricate formula works by first identifying the row numbers of matches, then sorting those matches, and finally retrieving the data associated with those sorted row numbers. This sequential execution ensures that the returned values are listed consecutively without gaps, even if the matching data in the source table is scattered.

1. The IF Statement (IF(D$2=$B$1:$B$14, ...)): This is the core logical test. It checks every cell in the criteria range ($B$1:$B$14) against the specified criteria (D$2). If the condition is TRUE (a match is found), it proceeds to the next step. If it is FALSE, it returns an empty string ("").

2. The MATCH/ROW Combination (MATCH(ROW($B$1:$B$14), ROW($B$1:$B$14))): This is a sophisticated way to generate a sequential list of row numbers corresponding to the data set (in this case, 1 through 14) for only the rows where the condition was true. When combined with the IF function, it effectively creates an array containing only the relative row indices of the matching data, interspersed with empty strings where no match occurred.

3. The SMALL Function (SMALL(..., ROWS($A$1:A1))): The SMALL function is critical for iteration. It takes the array of row indices generated by the IF statement and finds the K-th smallest value. The ROWS($A$1:A1) part determines K. When you drag the formula down one cell (e.g., to A2), ROWS($A$1:A2) becomes 2, retrieving the second smallest row number. This mechanism ensures that the results are pulled sequentially, one match per cell, until all matches are retrieved.

4. The INDEX Function (INDEX($A$1:$A$14, ...)): Finally, the INDEX function uses the row number outputted by SMALL to look up and return the actual corresponding value from the designated return range ($A$1:$A$14). Since SMALL systematically returns the 1st, 2nd, 3rd, etc., matching row index, INDEX retrieves the corresponding data points in order.

The careful use of absolute and relative referencing (dollar signs, e.g., $A$1 vs A1) is paramount here. The criteria range, return range, and the overall row reference arrays must be absolute (e.g., $A$1:$A$14) so they do not shift when the formula is dragged down. Conversely, the iteration counter (ROWS($A$1:A1)) must be partially relative to allow the lower bound (A1) to remain fixed while the upper bound (A1, A2, A3…) expands, increasing the K value for the SMALL function.

Case Study: Retrieving NBA Finals Winners Data

To demonstrate the practical application of this powerful Array Formula, we will use a common scenario involving historical sports data. Suppose we have a dataset tracking the NBA finals winners over several years and we want to isolate all years associated with a specific team. This scenario perfectly illustrates the need for a multi-value return based on a single team name criteria.

Consider the following hypothetical dataset established in Google Sheets, spanning cells A1 through B14. Column A contains the Year (the value we want to return), and Column B contains the Winning Team (the criteria range).

The structure of our data is as follows:

Our objective is to create a dynamic lookup that, when given a team name in a separate cell, returns a sequential list of all corresponding years in a new column. For this example, we will begin by attempting to return every year that the Golden State Warriors won the NBA championship, using “Warriors” as our initial criteria.

Step-by-Step Implementation and Initial Result

The implementation requires careful placement of both the criteria and the formula. We must designate a cell to hold our lookup criteria, allowing easy modification later, and then input the array formula into the first cell of the results column.

1. First, define the criteria: Type the team name, Warriors, into cell D2. This cell will serve as the reference point (D$2) for the conditional check within our formula.

2. Next, enter the complete formula into cell E2. Since the criteria range is B1:B14 and the return range is A1:A14, the formula, when adapted to these specific ranges, looks exactly like the generalized formula introduced earlier:

   =INDEX($A$1:$A$14, SMALL(IF(D$2=$B$1:$B$14, MATCH(ROW($B$1:$B$14), ROW($B$1:$B$14)), ""), ROWS($A$1:A1)))

3. Press Enter. Because the ROWS($A$1:A1) component is currently equal to 1, the formula executes the SMALL function to find the first (K=1) smallest row number where the criteria “Warriors” is found. The result will display the earliest year corresponding to a Warriors victory.

Upon pressing Enter, the first matching value, 2015, will be correctly displayed in cell E2, confirming that the formula structure is sound and has successfully identified the first match within the dataset.

Iterating Results and Handling the Error Condition

The true utility of this array method emerges when we populate the formula across multiple cells. Since the formula is constructed to dynamically iterate through the sorted list of matching row numbers, dragging the formula down column E will sequentially reveal all subsequent years the Warriors won the championship.

To retrieve all matching values, simply select cell E2 and drag the fill handle (the small square at the bottom right corner of the cell) downwards into the cells below (E3, E4, E5, etc.). As the formula moves down, the ROWS($A$1:A1) reference expands, increasing K, allowing SMALL to look for the second, third, and fourth smallest row indices, respectively.

The iteration continues until the SMALL function attempts to retrieve a K-th value that does not exist because all available matches have already been returned. At this point, the function will return the standard error value for a lack of numerical result: #NUM!. This error serves as a clear indicator that the list of matching results is complete.

Based on the results generated by the array formula in column E, we can confirm that the Warriors won the finals during the following years:

• 2015
• 2017
• 2018
• 2022

Creating a Dynamic Lookup Criteria

One of the most powerful aspects of defining the criteria in a separate cell (D2) is the inherent dynamic nature of the entire structure. If the team name in cell D2 is changed, the entire list of results in column E automatically recalculates and updates instantly to reflect the new criteria, without needing to modify the complex formula itself.

For instance, if we update cell D2 from “Warriors” to “Lakers,” the array formula immediately recalculates the row indices, the SMALL function retrieves the relevant rows for the Lakers, and the INDEX function pulls the correct years. The list of results in column E will instantly reflect this change, demonstrating the efficiency and versatility of this method for dynamic reporting and analysis.

The updated list accurately reflects the years the Los Angeles Lakers won the championships according to our dataset:

• 2010
• 2020

Users are encouraged to experiment by changing the team name in cell D2 to any team present in the criteria column (B1:B14) to observe the instant, dynamic updating of the results. This dynamic mechanism is particularly useful in dashboards or summary sheets where the criteria often changes based on user input or external dependencies.

Alternative and Modern Solutions: FILTER and QUERY Functions

While the nested INDEX/SMALL/IF formula provides a robust, universally compatible method for multi-value lookup, modern Google Sheets users often prefer simpler, dedicated functions. The two primary alternatives that achieve the exact same result—returning multiple values based on a single criterion—are the FILTER function and the QUERY function. These functions handle the array processing implicitly, leading to much cleaner and more readable syntax.

The FILTER function is perhaps the most direct replacement for the complex array formula. It takes the range you want to return (Column A) and a condition (Column B equals D2). Unlike the INDEX/SMALL method which requires copying the formula down and generates errors when matches run out, the FILTER function is a spill-over function, meaning a single formula in cell E2 would automatically populate all results below it. For our NBA example, the equivalent formula would be: =FILTER(A1:A14, B1:B14 = D2). This simplicity drastically reduces complexity and potential error points.

Similarly, the QUERY function offers unparalleled flexibility, especially when dealing with multiple criteria, sorting, or aggregation requirements. The QUERY function uses the Google Visualization API Query Language, which resembles SQL, allowing users to define exactly what data to select and what conditions to apply. For the same NBA example, the formula would be: =QUERY(A1:B14, "SELECT A WHERE B = '" & D2 & "'", 0). While slightly more complex than FILTER, QUERY is the ultimate tool for highly sophisticated data extraction and transformation within Google Sheets.

Summary of Data Extraction Techniques

Retrieving multiple values based on a single criterion is a fundamental skill in advanced spreadsheet management. Whether utilizing the traditional, robust INDEX/SMALL/IF Array Formula or embracing modern solutions like FILTER and QUERY, the goal remains the same: efficient, dynamic, and accurate data extraction. The INDEX/SMALL approach is valuable for understanding the mechanics of array processing and for compatibility in environments where spill-over functions might be restricted. However, for most modern Google Sheets applications, FILTER or QUERY provide superior readability and ease of maintenance.

The choice of method depends heavily on the complexity of the criteria and the user’s proficiency. For simple single-criteria lookups, FILTER is recommended due to its concise syntax. For situations requiring sorting, grouping, or highly complex conditional logic, the QUERY function is the industry standard. Regardless of the tool chosen, mastering the ability to retrieve entire sets of data based on dynamic criteria significantly enhances productivity and analytic capability within the spreadsheet environment.

Further Learning and Related Tutorials

To continue developing expertise in complex data manipulation within Google Sheets, consider exploring related tutorials that build upon the concepts introduced here. These advanced functions are often combined to solve intricate business problems, such as handling unique entries or performing conditional aggregation.

The following tutorials explain how to perform other common tasks in Google Sheets: