Vlookup Macro to Return All Matching Results and Stack them with Previous Results

This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular search value; however, this one will stack the results. This means that, each time you run the macro, the new results will appear below the old results.

This macro runs on the currently visible worksheet by default but it would be easy to make it run on a specific worksheet. The benefit of having this macro stack the new results below the old ones is that you could have a dynamically updated sheet from which you wanted to return results and you could use this macro to store specific results before the dynamically updated data changes.

Sub Return_Results_Sheet()

'location of the cell used to make the search
searchValue = Range("A2")

'which column to search
searchCol = 6

'which column to return the value from
returnValueCol = 7

'which column to output the resutls in
outputValueCol = 2

'which row to start the output
outputValueRowStart = 2

lastRow = Cells(Rows.Count, searchCol).End(xlUp).Row


For i = 1 To lastRow

    checkValue = Cells(i, searchCol).Value
    
    If checkValue = searchValue Then
        
        'get the value we will need to return
        returnvalue = Cells(i, returnValueCol)
    
        'get last row for the output column
        nextOutputRow = Cells(Rows.Count, outputValueCol).End(xlUp).Row + 1
        
        'Make sure the next output row is >= the outputValueRowStart variable
        ' or then set the nextOutputRow to the value of the outputValueRowStart
        If nextOutputRow < outputValueRowStart Then
        
            nextOutputRow = outputValueRowStart
        
        End If
        
        'output the value to the correct cell
        Cells(nextOutputRow, outputValueCol).Value = returnvalue
    
    End If

Next i


End Sub

The code above is commented and pretty self-explanatory but, of course, I'll provide some additional help below that you may need to customize the macro to work in your Excel file.

Note that columns are referenced in three variables, searchCol, returnValueCol, and outputValueCol, and that the numeric value is used to reference the columns. So, instead of column A, a 1 is used; for column B, a 2 would be used. This is very important and these references must be left as numbers. To get the number of a far-off column, just go to any cell in that column and input the function =COLUMN() and hit enter and you will then see that column's number.

The searchValue is the cell reference of the cell that is used to locate the data to return.

The searchCol value tells the macro which column to look through to find the searchValue.

The returnValueCol is used to say which column you want to get the data from once the searchValue has been located in the searchCol. The data must be located in the same row as the matching searchValue.

The outputValueCol is the column where you want to return the results.

The outputValueRowStart is the first row in which the returned results should be stored.

As mentioned above, this macro works on the currently visible worksheet, but it is easy to change it to work for specific or non-visible worksheets as well.

I hope this helps! :)

---