 |
|
|
|
|
|
|
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"
OK
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