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,ROW(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