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.

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