VLookup with wild card, make it keep going

If you want to find all results using a formula and then easily go through them, you can use a formula like this:

=IFERROR(INDEX($B$1:$B$7, SMALL(IF($A$1=$A$1:$A$7, ROW($A$1:$A$7)-ROW($A$1)+1), ROW(1:1))),"")

It's a confusing formula, but you can view our tutorial on teachexcel that explains it:

Vlookup to Return All Matching Results

However, to do exactly what you want requires a bit more effort.

Run Vlookup to Return New Result on Tab Key

Put this into a module:

Public curRow As Long
Sub Return_Results_Sheet_Tab()

'cell that contains the value for which you are searching
searchValue = Range("A2")

'number of the column you will search through to find the searchValue
searchCol = 6

'number of the column that contains the data you want to return
'the data is returned from the same row that contains a value that matches the searchValue
returnValueCol = 7

'the column where you want to store the returned results
outputValueCol = 2

'the row in which you want to start the list of returned results
'everything from this row down must be empty!
outputValueRowStart = 2

'find the last row so don't have to search every cell
lastRow = Cells(Rows.Count, searchCol).End(xlUp).Row

'clear the results display area
Range(Cells(outputValueRowStart, outputValueCol), Cells(Rows.Count, outputValueCol)).Clear


'search for the content and return it if a match is found
'use a for loop here becauase this is a simple check
For i = 1 To lastRow

    'get the value that is cheched against the searchValue
    checkValue = Cells(i, searchCol).Value

    'if a match is found, do everything else
    If checkValue Like "*" & searchValue & "*" And i > curRow Then

        'set the current row for future reference
        curRow = i

        'get the value we will need to return
        returnvalue = Cells(i, returnValueCol)

        'output the value to the correct cell
        Cells(outputValueRowStart, outputValueCol).Value = returnvalue

        Exit Sub

    End If

Next i


End Sub

Put this into the worksheet that will return the results:

Private Sub Worksheet_Change(ByVal Target As Range)
'cell reference for where you will type the "search value" for the 'vlookup'
If Target.Address <> "$A$2" Then Exit Sub

curRow = 1

End Sub

Put this into the ThisWorkbook section:

Option Explicit

Private Sub Workbook_Activate()
    Application.OnKey "{TAB}", "Return_Results_Sheet_Tab"
End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "{TAB}"
End Sub

The code is pretty heavily commented and so that should explain everything.

The one thing to note is that this example assumes that everything, the lookup value, returned results, and lookup table will all be on the same worksheet. You can change that if you want by updating the macros to have sheet specific references and you can find tutorial on that topic on teachexcel.

Quick Notes:

By default, the macro assumes this...

A2 is the cell for the value that you want to lookup.

F is the column to search through to find a match.

G is the column from which a result will be returned when a match is found.

2 is the column and row where the result will be returned (cell B2).

(You can change all of this by looking through the macro and reading the comments.)

Also, the bulk of the macro was taken from a tutorial here on teachexcel, and you can read more about it here:

Vlookup Macro to Return All Matching Results from a Sheet in Excel