Vlookup when multiple values can be returned

  • Thread starter Thread starter dennis.davis
  • Start date Start date
D

dennis.davis

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
 
Try this array formula** :

Where:

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(ToolOrd,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)
 
Try this array formula** :

Where:

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










- Show quoted text -
Click to expand...


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.
 
Try this (still an array formula) but with 1000+ rows *expect* it to be
slow:

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

Copy across

--
Biff
Microsoft Excel MVP


Try this array formula** :

Where:

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










- Show quoted text -
Click to expand...


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.
 
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
 
Try this (still an array formula) but with 1000+ rows *expect* it to be
slow:

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

Copy across

--
Biff
Microsoft Excel MVP







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 -
Click to expand...

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
 
Try this (still an array formula) but with 1000+ rows *expect* it to be
slow:

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

Copy across

--
Biff
Microsoft Excel MVP







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 -
Click to expand...

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
 
Here's a small sample file that demonstrates this:

Dennissample.xls 17kb

http://cjoint.com/?cttuvgVBHP


--
Biff
Microsoft Excel MVP


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

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

Copy across

--
Biff
Microsoft Excel MVP







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 -
Click to expand...

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
 
Here's a small sample file that demonstrates this:

Dennissample.xls  17kb

http://cjoint.com/?cttuvgVBHP

--
Biff
Microsoft Excel MVP









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 -
Click to expand...


That works magically. Thanks for your help.
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


Here's a small sample file that demonstrates this:

Dennissample.xls 17kb

http://cjoint.com/?cttuvgVBHP

--
Biff
Microsoft Excel MVP









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 -
Click to expand...


That works magically. Thanks for your help.
 
Back
Top