vlookup to return multiple results for a cell

[rooster05]

hello all

I am in need of help please, i have a spreadsheet with various tabs, in tab1, column H i have various product codes which will be my base lookup value, this product code may have had various changes, these changes are recorded in tab4 with the code in column A, and the old value in column E and the new value in column F and the date change in column I. what i would like is a formula that when the product code is looked at it will return all the old values and the date(s) it was changed.

i have attempted with vlookups and if statements but unsuccessfully

any help would be very much appreciated

 

[DanteAmor]

If on tab 1, in column H you have several product numbers, then where do you want the results? You could give an example.

 

[rooster05]

Thanks for the reply
Sorry, I would like the results to be on tab 1, column Q (this is an empty column so results can go down several rows)

 

[DanteAmor]

Try this.
results in Q and R

Sub multiple_results()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim c As Range, r As Range, b As Range, celda As String
    Dim j As Long
    
    Set sh1 = Sheets(1)
    Set sh2 = Sheets(4)
    
    Set r = sh2.Range("A:A")
    j = 2
    For Each c In sh1.Range("H2", sh1.Range("H" & Rows.Count).End(xlUp))
        Set b = r.Find(c.Value, LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                'detalle
                sh1.Cells(j, "Q").Value = b.Value
                sh1.Cells(j, "R").Value = sh2.Cells(b.Row, "I").Value
                j = j + 1
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        End If
    Next
    MsgBox "End"
End Sub

 

[rooster05]

Thank you, i will look at tomorrow when i'm back at work, thanks for posting this so quickly

 

[rooster05]

Thanks this worked as hoped

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.