Vlookup when multiple values can be returned

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
 
T

T. Valko

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)
 
D

dennis.davis

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.
 
T

T. Valko

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.
 
J

JMB

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
 
D

dennis.davis

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
 
D

dennis.davis

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
 
T

T. Valko

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
 
D

dennis.davis

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.
 
T

T. Valko

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

lookup/match mult values 3
vlookup, indexing, oh my.... 4
Lookup function 2
vlookup backward 8
SumProduct with dates 4
Vlookup multiple values 4
Count columns that meet criterias 3
If, Lookup, Match or a combination of all 3

Top