Premium Excel Course Now Available!
Build Professional - Unbreakable - Forms in Excel
45 Tutorials - 5+ Hours - Downloadable Excel Files
Instant Access! - Lifetime Access!
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
Add to Favorites
Favorited
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! :)
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Instruction
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
View Course
()
Vlookup Macro to Return All Matching Results from a Sheet in Excel
Macro: This Excel Macro works like a better Vlookup function because it returns ALL of the matchi...
Excel Macro that Searches Entire Workbook and Returns All Matches
Macro: This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workboo...
Pass Arguments to a Macro Called from a Button or Sheet in Excel
Tutorial: How to pass arguments and values to macros called from worksheets, buttons, and anything e...
Vlookup to Return All Matching Results
Tutorial:
Here is an Excel formula that will act like a Vlookup that returns every matching result ...
Vlookup Date Picker in Excel (Dynamic)
Tutorial: Make a dynamically updating vlookup date picker for excel that allows you to choose a date...
Excel Function to Remove All Text OR All Numbers from a Cell
Tutorial: How to create and use a function that removes all text or all numbers from a cell, whichev...
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.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- 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.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go 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.