vlookup returning multiple values

[BurkeDad]

I need a way to look up one criteria in a table that could return multiple answers.
Okay here is the long view. The criteria is a facility ID number and I need to have a formula return multiple values in a list. This criteria is in multiple rows and I need the formula to return the infomormation from each row.
I was thinking of using a vlookup but I know this will return the first value that matches the criteria. However, I need to have the infomration from each row returned to another sheet in a list format.

Will this require the use of an array formula?

 

[pboltonchina]

This will put all items from Column A that are the same into a seperate sheet for each unique value. Try it on a copy of your data to see if it's what you want.
[/CODE]
Option Explicit

Sub ParseItems()
'Jerry Beaucaire (11/11/2009)
'Based on selected column, data is filtered to individual sheets
'Creates sheets and sorts sheets alphabetically in workbook
'6/10/2010 - added check to abort if only one value in vCol
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
Dim WS As Worksheet, MyArr As Variant, vTitles As String, Oops As Boolean

Application.ScreenUpdating = False

'Column to evaluate from, column A = 1, B = 2, etc.
vCol = 1

'Sheet with data in it
Set WS = Sheets("Sheet1")

'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
vTitles = "A1:E1"

'Spot bottom row of data
LR = WS.Cells(WS.Rows.Count, vCol).End(xlUp).Row

'Get a temporary list of unique values from column A
WS.Columns(vCol).SpecialCells(xlConstants).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=WS.Range("EE1"), Unique:=True

'Sort the temporary list
WS.Columns("EE:EE").Sort key1:=WS.Range("EE2"), _
Order1:=xlAscending, Header:=xlYes, ordercustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Check for more than one value in list
If WS.Range("EE" & Rows.Count).End(xlUp).Row > 2 Then

'Put list into an array for looping
'(values cannot be the result of formulas, must be constants)
MyArr = Application.WorksheetFunction.Transpose(WS.Range("EE2:EE" _
& Rows.Count).SpecialCells(xlCellTypeConstants))

'clear temporary worksheet list
WS.Range("EE:EE").Clear

Else
WS.Range("EE:EE").Clear
Oops = True
GoTo ErrorExit
End If

'Turn on the autofilter, one column only is all that is needed
WS.Range(vTitles).AutoFilter

'Loop through list one value at a time
For Itm = 1 To UBound(MyArr)
WS.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)

If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then 'create sheet if needed
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = MyArr(Itm)
Else 'clear sheet if it exists
Sheets(MyArr(Itm)).Move after:=Sheets(Sheets.Count)
Sheets(MyArr(Itm)).Cells.Clear
End If

'customize this section as needed for copy/paste targets
WS.Range("A" & WS.Range(vTitles).Resize(1, 1).Row & ":A" & LR) _
.EntireRow.Copy Sheets(MyArr(Itm) & "").Range("A1")


WS.Range(vTitles).AutoFilter Field:=vCol
MyCount = MyCount + Sheets(MyArr(Itm)) _
.Range("A" & Rows.Count).End(xlUp).Row - 1
Sheets(MyArr(Itm)).Columns.AutoFit
Next Itm

'Cleanup
WS.AutoFilterMode = False
MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " _
& MyCount & vbLf & "Hope they match!!"

ErrorExit:
If Oops Then MsgBox "Only one value found, aborting parse process..."
Application.ScreenUpdating = True
End Sub
[/CODE]

 

[Robert Mika]

Look at this thread:

http://www.mrexcel.com/forum/showthread.php?p=2263339#post2263339

Is this something you are after?