Find all rows in another sheet matching key

Question

I have two sheets in my Excel workbook, one called Customer and the other called Product:

I wish to make a Lookup table in the Customer sheet, where in for example cell G2, above the lookup table, I put in the customer_id I wish to find all rows for in the sheet called Product. So, for example if I in G2 put in "1", I will get the two matching rows in sheet "Product". I have tried using VLOOKUP, but I always just match the first row, and none of the others.

EDIT: in the lookup table you will only get the information in the sheet named "Product": if you put in "1" you will get rows 2 and 3 from the Product sheet. Customer_id only has one row per customer_id in the sheet called "customer", whereas in the sheet called "Product" you can have many rows per customer_id

Answer 1

Your source data for Pivot Table would be your Product Sheet:

Create a Pivot Table, and take Customer Field to filter section in the setup:

Just changing manually the number in Cell B2 will return the Product related To that customer id.

NOTE: If you input a Customer_id that is not in the Product Sheet, an alert Msgbox will pop up warning you about that. So if you see at any moment that alert, it means that customer_id has 0 records in PRoduct Sheet :)

Hope this helps

Answer 2

You could try Workbook_Change:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim wsCus As Worksheet, wsPro As Worksheet
    Dim LastRow As Long, ID As Long, i As Long
    Dim arr As Variant
    Dim FullRecord

    With ThisWorkbook
        Set wsCus = .Worksheets("Customer")
        Set wsPro = .Worksheets("Product")
    End With

    If Not Intersect(Target, wsCus.Range("G2")) Is Nothing And Target.Count = 1 Then

        ID = Target.Value

        With wsPro

            LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

            arr = .Range("A2:A" & LastRow)

            For i = LBound(arr) To UBound(arr)

                If arr(i, 1) = ID Then

                    If FullRecord = "" Then
                        FullRecord = i + 1
                    Else
                        FullRecord = FullRecord & ", " & i + 1
                    End If

                End If

            Next i

            Application.EnableEvents = False
                If FullRecord = "" Then
                    wsCus.Range("I2").Value = "No match found"
                Else
                    wsCus.Range("I2").Value = "Matched lines for ID (" & ID & "): " & FullRecord
                End If
            Application.EnableEvents = True

        End With

    End If

End Sub

Instructions:

1. Once you have your workbook open press ALT & F11 to open VBA Editor.
2. Double click on the Customer sheet on the left upper part.
3. Select Worksheet.
4. Change event.
5. Paste the code as you see in the picture.
6. Then save the workbook as Excel Macro - Enabled Workbook, close VBA Editor and change G2 value.

Steps:

Results:

Find Match

No Match