VLookup with wild card, make it keep going

0

I use V Lookup in wild card format VLookup("*"&A2&"*",  etc).  It is searching a column for a match to A2. When it finds the first such match, it stops.  The problem is that the column may have more matches to A2 beyond the first.  I want a method to essentially re-start VLookup at the next row beyond the first match so that it continues until the next match. Then if this is not what I'm looking for, restart it again below the 2nd, 3rd, and so on matches. 

Answer
Discuss

Discussion

Do you want the formula to return all matches or just allow you to essentially "tab" through the matches until you find what you want?
don (rep: 1322) Jun 16, '17 at 10:05 am
I want to tab through although listing all would be useful in other situations. 
grtblu Jun 17, '17 at 7:50 pm
Check out my answer and if it works, make sure to select it.
don (rep: 1322) Jun 22, '17 at 3:05 am
Add to Discussion

Answers

0

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.

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

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login