You can use the following formula to find multiple values in Excel:
=INDEX($A$1:$B$12,SMALL(IF($A$1:$A$12=$F$1,ROW($A$1:$A$12)),ROW(1:1)),2)
This particular formula finds all of the values in the range B1:B12 where the corresponding value in the range A1:A12 is equal to the value in cell F1.
The following example shows how to use this formula in practice.
Example: Find Multiple Values in Excel
Suppose we have the following dataset in Excel that shows which employees sold various products at some company:
Now suppose we would like to find all of the products sold by Mike.
To do so, we can type his name in cell D2:
Then we can type the following formula into cell E2:
=INDEX($A$1:$B$12,SMALL(IF($A$1:$A$12=$D$2,ROW($A$1:$A$12)),ROW(1:1)),2)
This will return the first product sold by Mike:
We can then autofill this formula down to the remaining cells in column E to find all products sold by Mike:
We can now see all four products sold by Mike:
- Oranges
- Kiwis
- Apples
- Bananas
We can look at the original data in columns A and B to confirm that Mike indeed sold all four of these products.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Count Number of Occurrences in Excel
How to Count Frequency of Text in Excel
How to Calculate Relative Frequency in Excel