When working with large datasets in Excel, finding all matches for a specific value can be challenging. Traditional VLOOKUP functions only return the first match they find, but with a bit of creativity, you can retrieve all matches and list them neatly. In this blog, we’ll show you a simple trick to achieve this using a combination of Excel functions.
Let's start with some sample data. Assume we have the following dataset in Excel:
Product ID |
Product Name |
Price |
101 |
Widget A |
25 |
102 |
Widget B |
30 |
101 |
Widget A |
25 |
103 |
Widget C |
35 |
102 |
Widget B |
30 |
101 |
Widget A |
25 |
We want to find all instances of a specific Product ID (e.g., 101) and list all the matching rows.
Ensure your data is in a structured format as shown above. In this example, our data range is A1.
Add a helper column to identify matching rows.
=IF(A2=$F$2, ROW(), "")
Here, $F$2 is the cell where we will input the Product ID we are searching for.
In cell F2, enter the Product ID you want to search for (e.g., 101).
Now, we need to extract all rows that match the criteria.
IFERROR(INDEX(A:A, SMALL($D$2:$D$6, ROW(A1))), "")
This formula extracts the Product ID based on the row numbers identified in the helper column.
IFERROR(INDEX(B:B, SMALL($D$2:$D$6, ROW(A1))), "")
This formula extracts the Product Name based on the row numbers identified in the helper column.
IFERROR(INDEX(C:C, SMALL($D$2:$D$6, ROW(A1))), "")
This formula extracts the Price based on the row numbers identified in the helper column.
By entering 101 in cell F2, the extracted data will appear in columns G, H, and I:
Extracted Product ID |
Extracted Product Name |
Extracted Price |
101 |
Widget A |
25 |
101 |
Widget A |
25 |
101 |
Widget A |
25 |
With this crazy simple trick, you can use Excel formulas to find and list all matches for a specific value. This method leverages helper columns and array formulas, making it an efficient solution for working with large datasets. Try it out with your own data and see how it can streamline your data analysis tasks!