Lisa,

The "vlookup" function can't return more than one value because a cell can't
hold more than one value. I would suggest writing a macro using the Find
method.

Here's an example of code I frequently use to accomplish this:

With ActiveSheet.Range("a1:a65536")
Set c = .Find(gathNum, LookIn:=xlValues) ' Search within top row
If Not c Is Nothing Then
findFirstRow = c.row
Do
findMatchRow = c.row
If (something) Then
do something (like copy the value to an array or another
spreadsheet)
Else
Set c = .findNext(c)
End If
Loop Until (c Is Nothing Or c.row = findFirstRow)
End If
End With

Be very careful to set up your conditions correctly, or you will cause an
infinite loop. (If you do, use, Ctrl+BreaK to stop the code.) Read the help
files for more info.

Good luck,
Pflugs

"lmarquis" wrote:

Spreadsheet 1 has A = CusPN
Spreadsheet 2 has A= CusPN and B= MfgPN

One CusPN represents up to 20 different MfgPN, thus when I do a Vlookup I
only get the first MfgPN from Spreadsheet 2. I need to return all results.
I am guessing I can use a table array and set up as a macro for everyday use.
Can someone help? The worksheet is 7 Columns wide total (A-G). I know how
to set this up as a macro and have tried to make this work but have had no
luck. PLEASE help! My whole spreadsheet is 30K lines long and I have been
doing this weekly manually. My eyes are bleeding... Ha Ha
Thank you,
Lisa