MATCH multiple values

[Dave M]

I have an employee table. In it there is a badge number in column 5. Emp.
name is column 1. Each emp. can have multiple badge numbers. When they do,
their name is repeated in the next row with the second badge number. They
can have up to three badges.

I need to create a lookup for all employees, but need the multiple badge
numbers in columns beside their name, instead of rows with multiple names,
and their name listed only once.

Any ideas?

Thanks

Dave

 

[stephen.h.dow]

If headers are in row 1 and data (names) start in A2 and badges start
in E2, then this should work:

Lookup name in K2
Lookup first badge in L2: =VLOOKUP(K2,$A$2:$E$12,5,0)
Lookup second badge in M2: =IF(OFFSET($A$2,MATCH(K2,$A$2:$A$12,0),
0)=K2,OFFSET($A$2,MATCH(K2,$A$2:$A$12,0),4),"No badge")
Lookup third badge in N2: =IF(OFFSET($A$2,MATCH(K2,$A$2:$A
$12,0)+1,0)=K2,OFFSET($A$2,MATCH(K2,$A$2:$A$12,0)+1,4),"No badge")

Hope that helps..

 

[Don Guillett]

Modify this to suit
Sub findbadgenums()
On Error Resume Next
For I = Cells(Rows.Count, "a").End(xlUp).row To 3 Step -1
With Worksheets("sheet1").Range("e2:e18")
Set c = .Find(Cells(I, 1), LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
lc = Cells(I, Columns.Count).End(xlToLeft).Column + 1
Cells(I, lc) = c.Offset(, 1)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
Next I
End Sub