On Feb 19, 12:22*pm, "T. Valko" wrote:
Here's a small sample file that demonstrates this:

Dennissample.xls *17kb

http://cjoint.com/?cttuvgVBHP

--
Biff
Microsoft Excel MVP

wrote in message

...
On Feb 18, 4:25 pm, "T. Valko" wrote:





Try this (still an array formula) but with 1000+ rows *expect* it to be
slow:

=IF(COLUMNS($B2:B2)<=COUNTIF(ToolNum,$A2),INDEX(To olOrd,SMALL(IF(ToolNum=$A**2,ROW(ToolOrd)-MIN(ROW(ToolOrd))+1),COLUMNS($B2:B2))),"")

Copy across

--
Biff
Microsoft Excel MVP

wrote in message

...
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.- Hide quoted text -

- Show quoted text -

Thanks for the help, but maybe I'm doing something wrong. *Here is the
formula I entered and then drug down the column. *I don't get any
results in the cells, only blanks.

=IF(COLUMNS('ICAS Data'!$B2:'ICAS Data'!B2)<=COUNTIF('ICAS Data'!
$A2,$A2),INDEX('ICAS Data'!$B2,SMALL(IF('ICAS Data'!$A2=$A2,ROW('ICAS
Data'!$B2)-MIN(ROW('ICAS Data'!$B2))+1),COLUMNS('ICAS Data'!$B2:'ICAS
Data'!B2))),"")

ICAS Data is the name of the sheet containing the Tool Number I need
to match in Col. A and the Tool Order I want retrieved in Col. B- Hide quoted text -

- Show quoted text -


That works magically. Thanks for your help.