VLOOKUP All Matches with This Crazy Simple Trick

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.

Sample Data

Let's start with some sample data. Assume we have the following dataset in Excel:

Goal

We want to find all instances of a specific Product ID (e.g., 101) and list all the matching rows.

Step 1: Setting Up the Data

Ensure your data is in a structured format as shown above. In this example, our data range is A1.

Step 2: Create a Helper Column

Add a helper column to identify matching rows.

1. In cell D1, enter the header "Match".
2. In cell D2, enter the formula:

=IF(A2=$F$2, ROW(), "")

Here, $F$2 is the cell where we will input the Product ID we are searching for.

3. Drag the formula down from D2 to D6.

Step 3: Enter the Search Criteria

In cell F2, enter the Product ID you want to search for (e.g., 101).

Step 4: Extract All Matches

Now, we need to extract all rows that match the criteria.

1. In cell G1, enter the header "Extracted Product ID".
2. In cell H1, enter the header "Extracted Product Name".
3. In cell I1, enter the header "Extracted Price".
4. In cell G2, enter the following array formula:

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.

5. In cell H2, enter the following array formula:

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.

6. In cell I2, enter the following array formula:

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.

7. Drag the formulas in G2, H2, and I2 down as far as necessary to ensure all matches are captured.

Detailed Explanation

• Helper Column (D): The formula =IF(A2=$F$2, ROW(), "") checks if the Product ID in column A matches the search value in F2. If it does, it returns the row number; otherwise, it returns an empty string.
• Array Formulas (G2, H2, I2): The INDEX and SMALL functions work together to extract values from the data range based on the row numbers identified in the helper column.
• SMALL($D$2:$D$6, ROW(A1)): This part of the formula returns the nth smallest value from the helper column, where n is the relative position of the formula (e.g., ROW(A1) returns 1, ROW(A2) returns 2, etc.).
• INDEX(A:A, ...) retrieves the value from column A (or B or C) at the row number returned by the SMALL function.
• IFERROR(..., "") handles any errors that occur when there are no more matches, returning an empty string instead.

Result

By entering 101 in cell F2, the extracted data will appear in columns G, H, and I:

Conclusion

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!