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! :)
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.