Using vlookup to populate multiple rows at once

[Andy Roberts]

I have 2 spreadsheets. the first contains all the purchase orders we issue
as follows:-

JobNo, Supplier, Fee

There maybe (and often is) more than one supplier per JobNo. In the second
spreadsheet is a job form and I want to show all the suppliers (based on the
JobNo) - one on each row. I can manage to show one supplier (the first one
the vlookup I'm using finds) but repeating the vlookup in another cell
simply shows the same supplier.
How can I show each supplier based on JobNo in separate cells (e.g. A1 shows
Supplier1, A2 shows Supplier 2 etc? The common link is the JobNo.

Regards

Andy
Excel 2013
Win 7 Pro

 

[Claus Busch]

Hi Andy,

Am Thu, 5 Sep 2013 16:12:11 +0100 schrieb Andy Roberts:

JobNo, Supplier, Fee

There maybe (and often is) more than one supplier per JobNo. In the second
spreadsheet is a job form and I want to show all the suppliers (based on the
JobNo) - one on each row. I can manage to show one supplier (the first one
the vlookup I'm using finds) but repeating the vlookup in another cell
simply shows the same supplier.

try:
=IFERROR(INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!A$2:A$100=1,ROW($1:$99)),ROW(A1))),"")
Into the part Sheet1!A$2:A$100=1 change the 1 to the JobNo you are
looking for and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.

 

[Andy Roberts]

Thanks Claus

Here is where I have got to with your suggestion:-

=IFERROR(INDEX([Admin.xlsx]POs!$H$9:$H$10000,SMALL(IF([Admin.xlsx]POs!E$9:E$10000=A1,ROW($1:$99)),ROW(W3))),"")

I'll explain what each bit means (I think)...

[Admin.xlsx]POs! This is the workbook and worksheet the data I'm looking
for is on.
$H$9:$H$10000 This is the range (on the POs worksheet) which contains the
supplier name.
SMALL Not sure what this bit does.
E$9:E$10000=A1 This is the range (on the POs worksheet) which contains the
JobNos. A1 is the cell ref on the job sheet which has the job no in it.
ROW(W3) I've used W3 as a cell which is blank at the moment so I can see
what it does but nothing appears in this cell.

I've entered the formula in a cel and set it up as an array but the cell is
blank (no error either)

Andy

"Claus Busch" wrote in message
Hi Andy,

Am Thu, 5 Sep 2013 16:12:11 +0100 schrieb Andy Roberts:

JobNo, Supplier, Fee

There maybe (and often is) more than one supplier per JobNo. In the second
spreadsheet is a job form and I want to show all the suppliers (based on
the
JobNo) - one on each row. I can manage to show one supplier (the first one
the vlookup I'm using finds) but repeating the vlookup in another cell
simply shows the same supplier.

try:
=IFERROR(INDEX(Sheet1!$B$2:$B$100,SMALL(IF(Sheet1!A$2:A$100=1,ROW($1:$99)),ROW(A1))),"")
Into the part Sheet1!A$2:A$100=1 change the 1 to the JobNo you are
looking for and enter the array formula with CTRL+Shift+Enter


Regards
Claus B.

 

[Claus Busch]

Hi Andy,

Am Thu, 5 Sep 2013 16:53:58 +0100 schrieb Andy Roberts:

=IFERROR(INDEX([Admin.xlsx]POs!$H$9:$H$10000,SMALL(IF([Admin.xlsx]POs!E$9:E$10000=A1,ROW($1:$99)),ROW(W3))),"")

SMALL(IF([Admin.xlsx]POs!E$9:E$10000=A1,ROW($1:$9991)),ROW(W1))
POs!E$9:E$10000=A1 you have to set A1 absolute. With
SMALL(........,ROW(W1)) you will get the first entry. If you copy down
W1 changes to W2 and you will get the second and so on.
But you must refer to the same JobNo.


Regards
Claus B.

 

[Claus Busch]

Hi Andy,

Am Thu, 5 Sep 2013 16:12:11 +0100 schrieb Andy Roberts:

I have 2 spreadsheets. the first contains all the purchase orders we issue
as follows:-

JobNo, Supplier, Fee

There maybe (and often is) more than one supplier per JobNo. In the second
spreadsheet is a job form and I want to show all the suppliers (based on the
JobNo) - one on each row. I can manage to show one supplier (the first one
the vlookup I'm using finds) but repeating the vlookup in another cell
simply shows the same supplier.
How can I show each supplier based on JobNo in separate cells (e.g. A1 shows
Supplier1, A2 shows Supplier 2 etc? The common link is the JobNo.

you can also do it with advanced filter. Start the advanced filter from
the second workbook and follow the assistent. Activate "Without
duplicates".


Regards
Claus B.

 

[Andy Roberts]

Thanks Claus

I've got it working with your suggestion as follows:-

=IFERROR(INDEX([Admin.xlsx]POs!$H$9:$H$10000,SMALL(IF([Admin.xlsx]POs!$E$9:$E$10000=$A$1,ROW($1:$9999)),ROW($A1))),"")
=IFERROR(INDEX([Admin.xlsx]POs!$H$9:$H$10000,SMALL(IF([Admin.xlsx]POs!$E$9:$E$10000=$A$1,ROW($1:$9999)),ROW($A2))),"")

etc

However if I now insert a row above the values which are being returned
disappear. I've looked at the formulas and nothing seems to have changed
and they are still as an array. Why is this happening?

Andy

"Claus Busch" wrote in message
Hi Andy,

Am Thu, 5 Sep 2013 16:53:58 +0100 schrieb Andy Roberts:

=IFERROR(INDEX([Admin.xlsx]POs!$H$9:$H$10000,SMALL(IF([Admin.xlsx]POs!E$9:E$10000=A1,ROW($1:$99)),ROW(W3))),"")

try:
=IFERROR(INDEX([Admin.xlsx]POs!$H$9:$H$10000,SMALL(IF([Admin.xlsx]POs!E$9:E$10000=A1,ROW($1:$9991)),ROW(A1))),"")



Regards
Claus B.