Using a lookup to return multiple values in one cell??

[zim_zimmer]

Ok, basically I want to do a lookup to return some figures... I know
all the VLOOKUP, etc... trouble is I don't just want to return one
figure. It may be 1 figure but then it may be about 9/10 as well.


I've got a list of order numbers and a list of orders that need an
invoice date next to them. So I've done a bog standard vlookup and it
only returns one figure where as there might be 10 invoices related to
1 order... make any sense :?


(Table 1)

Order Number................... Invoice Date

10010001....................... 22/11/05
10010001....................... 14/10/05
10010002....................... 25/09/05
10010002....................... 21/12/05
10010002....................... 23/11/05
10010002....................... 05/10/05
10010002....................... 15/11/05
10010003....................... 20/12/05
etc...


(Table 2)

Order Number.... Supplier.... Invoice Date

10010001........ xxxxxxxx.... *lookup from table 1*
10010002........ xxxxxxxx.... *lookup from table 1*
10010003........ xxxxxxxx.... *lookup from table 1*

 

[Bernie Deitrick]

Let's say that table 1 is in columns A and B. With the order number of interest entered into cell
D2, this array formula (entered in cell D3 using Ctrl-Shift-Enter):

=IF(COUNTIF(A:A,$D$2)>=ROW()-ROW($D$2),INDEX($B$1:$B$100,
LARGE(($A$1:$A$100=$D$2)*ROW($A$1:$A$100),ROW()-ROW($D$2))),"")

copied down for 9 or 10 rows will return all the data from column B associated with the order number
in cell D2.

HTH,
Bernie
MS Excel MVP