Table of Contents
To find multiple values in Excel, you can use the VLOOKUP, MATCH, INDEX, and COUNTIF functions. For example, if you wanted to find two different values within a range of cells, you could use the VLOOKUP and MATCH functions in combination. VLOOKUP would be used to find the first value, and MATCH would be used to find the second value. The INDEX and COUNTIF functions can also be used to search for multiple values within a range of cells.
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.