you could also try a macro. you'd have to change the range references to
your actual ranges. be sure to back up your workbook. if you are new to
macros, david mcritchie has some instructions on his site for navigating the
vba editor and how to copy/paste macros into your project
http://www.mvps.org/dmcritchie/excel/excel.htm
Sub Test()
Dim rngCriteria As Range
Dim rngToolNumber As Range
Dim rngCell As Range
Dim rngFound As Range
Dim strFirst As String
Set rngCriteria = Sheets("Sheet2").Range("A2:A3") '<<<<CHANGE
Set rngToolNumber = Sheets("Sheet1").Range("A2:A4") '<<<CHANGE
For Each rngCell In rngCriteria.Cells
On Error Resume Next
With rngToolNumber
Set rngFound = .Find( _
what:=rngCell.Value, _
after:=.Cells(.Rows.Count, 1), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False, _
matchbyte:=False)
End With
On Error GoTo 0
If Not rngFound Is Nothing Then
strFirst = rngFound.Address
Do
With rngCell.Parent
.Cells(rngCell.Row, _
.Columns.Count).End(xlToLeft)(1, 2).Value = _
rngFound(1, 2).Value
End With
Set rngFound = rngToolNumber.FindNext(after:=rngFound)
Loop Until strFirst = rngFound.Address
Set rngFound = Nothing
End If
Next rngCell
End Sub
" wrote:
On Feb 18, 3:55 pm, "T. Valko" wrote:
Try this array formula** :
Whe
ToolNum = Sheet1!A2:An
ToolOrd = Sheet1!B2:Bn
Enter this array formula** on Sheet2 B2:
=IF(ROWS(B$2:B2)<=COUNTIF(ToolNum,A$2),INDEX(ToolO rd,SMALL(IF(ToolNum=A$2,RĀ*OW(ToolOrd)-MIN(ROW(ToolOrd))+1),ROWS(B$2:B2))),"")
Copy down until you get blanks. You'll have to copy to a number of cells
that is equal to the maximum number of times any lookup_value appears in the
lookup_table. For example, in your sample data the lookup_value J123 appears
the max number of times, 2. So you need to copy the formula to at least 2
cells.
** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
--
Biff
Microsoft Excel MVP
wrote in message
...
I have a spreadsheet with data in the following manner
Sheet 1
Col A. Col. B
Tool Number Tool Order #
J123 T008
J598 T258
J123 T568
On Sheet 2 in Column A, cell A2 I have the values I would like to
lookup in Sheet 1 Col A.
I need to return all values in Column B (Tool Order) on Sheet 2.
Anyone have any insight?
Thanks,
Dennis- Hide quoted text -
- Show quoted text -
Thanks for the reply.
What would I need to do to get this where the different Tool Orders
could be displayed in different columns on the same row?
To clarify, my Sheet 2 has 1000+ rows of Tool Numbers to match to
sheet 1.
Thanks.