Ozgrid, Experts in Microsoft Excel Spreadsheets
Javascript DHTML Drop Down Menu Powered by dhtml-menu-builder.com

Active DataXL - Download


AnalyserXL - Download


DownloaderXL - Download


Smart VBA - Download


TraderXL - Download

Ozgrid Excel Newsletter. Excel Newsletter Archives  



Vlookup Any Occurrence


I'm often asked how to use VLOOKUP to lookup any occurrence, be it the 2nd, 3rd etc. It can be done, but we need to add a helper column. I'm going to use column "A" as my helper column, while column B are persons names that I need to lookup any occurrence. My table range is B1:G9 with row 1 being column headers. In A2, enter this formula and copy down;

=B2 & COUNTIF($B$2:B2,B2)

Name our table, including our helper column and headings, "Table".

It is important to note that $B$2 for the start of our COUNTIF range is absolute and the second occurrence is relative. This means the range expands to $B$2:B3, $B$2:B4 etc when copied down. As column "B" are names I end up with Dave1, Peter1, Dave2... etc. Once copied down, column "A" can be hidden (optional).

I'm going to use another Worksheet (named "Lookup") to perform my VLOOKUP and make it user friendly. My table Worksheet is called "Table". 1st we need to create a unique list of our names in Column "B". So, activate the second sheet ("lookup") and activate AdvancedFilter from the Data tab.

Check "Copy to another location"

List range: Table!$B$2:$B$9

Criteria range: Leave blank

Copy to: Lookup!I1

Check "Unique records only"


It is important to note that we activate AdvancedFilter on our "Copy to another location" Sheet ("Lookup"). This is because AdvancedFilter would give us a message that we can't copy our unique list to another sheet if we activated AdvancedFilter on our "Table" Sheet.

I only have 3 unique names in I2:I4, I1 is our heading "Person". Let's change I1 to "Unique Names" and Name I2:I4 "UniqueNames".

Come back to A1:B1 and put the headings "Choose Occurrence" and "Choose Names" respectively.

Select A2 and activate Data Validation and use the "List:" option and type: 1,2,3 in the "Source:" box.

Select B2 and activate Data Validation and use the "List:" option and enter: =UniqueNames in the "Source:" box

Name A2: Occurrence

Name B2: Name

In A5 Enter: ="looking up " & CHOOSE(A2,"1st", "2nd","3rd") & " occurrence of " & Name

In A6 Enter: =Table!B1 and copy across to F6. This will give us copies of all our table headings.

In A7 Enter: =VLOOKUP(Name&Occurrence,Table,COLUMN()+1,FALSE) and copy across to F7.

This will return ALL columns from our lookup result. We use: COLUMN()+1 to return the relative Column from our "Table" range. The COLUMN Function returns the column number of the column it's used in.

Now, as you change the Named cells "Occurrence" and "Name" our Vlookup will return all matching columns.


VBA Sending the Record Back


Now we have 1 whole record from our "Table" range we can edit the record and send it back to our "Table" with use of VBA Code.

First we need to name A6:F6 "Results"
The VBA code;

Sub SendBack()
Dim strOccurence As String
Dim rPaste As Range

'Join our name and occurence
strOccurence = Range("Name") & Range("Occurence")

    'Work with left most column of our table
    With Range("Table").Columns(1)
       'Use the Find Method to locate mactching record
       'Set a Range Variable to our found cell
       On Error GoTo NoMatch 'Error trap for no match
       Set rPaste = .Find(What:=strOccurence, After:=.Cells(1, 1), LookIn:=xlValues, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False)(1, 2) 'Offset by 1 Column
        'Copy our returned record
        'Paste Special only values
        rPaste.PasteSpecial xlPasteValues
        'Clear the Clipboard
        Application.CutCopyMode = False
        'Put back our VLOOKUP functions
        Range("Results").FormulaR1C1 = "=VLOOKUP(Name&Occurence,Table,COLUMN()+1,FALSE)"
    End With
Exit Sub
NoMatch: MsgBox "No match found"

End Sub

Now assign the code to a Button from the Forms toolbar.

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft