Table of Contents
Excel is a powerful spreadsheet software that allows users to manipulate and analyze data. One of its useful features is the ability to return multiple values based on a single criteria. This means that users can specify a condition and Excel will search for all matching values and return them in a list. This feature is particularly useful for data analysis and reporting, as it eliminates the need for manual data sorting and filtering. By using Excel’s functions and formulas, users can easily retrieve multiple values that meet a specific criteria, saving time and effort.
Excel: Return Multiple Values Based on Single Criteria
You can use the following basic formula to return multiple values in Excel based on a single criteria:
=INDEX($A$1:$A$14, SMALL(IF(E$1=$B$1:$B$14, MATCH(ROW($B$1:$B$14), ROW($B$1:$B$14)), ""), ROWS($A$1:A1)))
This particular formula returns all of the values in the range A1:A14 where the corresponding value in the range B1:B14 is equal to the value in cell E1.
The following example shows how to use this formula in practice.
Example: Return Multiple Values Based on Single Criteria in Excel
Suppose we have the following dataset that shows the winner of the NBA finals during various years:
We can type the following formula into cell E2 to return every year that the Warriors were the winners:
=INDEX($A$1:$A$14, SMALL(IF(E$1=$B$1:$B$14, MATCH(ROW($B$1:$B$14), ROW($B$1:$B$14)), ""), ROWS($A$1:A1)))
Once we press Enter, the first year that the Warriors won will be shown:
We can then drag and fill this formula down to other cells in column E until we encounter a #NUM! value:
We can see that the Warriors won the finals during the following years:
- 2015
- 2017
- 2018
- 2022
If we change the team name in cell E1, the list of years will automatically update.
For example, suppose we type “Lakers” in cell E1 instead:
We can see that the Lakers won the finals during the following years:
- 2010
- 2020
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use Excel to return multiple values based on a single criteria?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-excel-to-return-multiple-values-based-on-a-single-criteria/
stats writer. "How can I use Excel to return multiple values based on a single criteria?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-excel-to-return-multiple-values-based-on-a-single-criteria/.
stats writer. "How can I use Excel to return multiple values based on a single criteria?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-excel-to-return-multiple-values-based-on-a-single-criteria/.
stats writer (2024) 'How can I use Excel to return multiple values based on a single criteria?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-excel-to-return-multiple-values-based-on-a-single-criteria/.
[1] stats writer, "How can I use Excel to return multiple values based on a single criteria?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use Excel to return multiple values based on a single criteria?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.