Vlookup when multiple values can be returned
 

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

Vlookup when multiple values can be returned
 

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

Vlookup when multiple values can be returned
 

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.

Vlookup when multiple values can be returned
 

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=$A2,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.

Vlookup when multiple values can be returned
 

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.

Vlookup when multiple values can be returned
 

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

Vlookup when multiple values can be returned
 

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

Vlookup when multiple values can be returned
 

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

Vlookup when multiple values can be returned
 

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.

Vlookup when multiple values can be returned
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


wrote in message
...
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.