I'm often asked how to use
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
=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.
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 Range("Results").Copy '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