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

[paul_mittoo]

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*




Any ideas??

 

[Guest]

I don't know how to do what you're after with a vlookup, but I'd be tempted
to use a pivot table instead. Use invoice number for field 1 of the pivot
table, and date for the second field - you'll end up with one occurrence of
each order no. in column 1 of the table, and then next to it a consecutive
list of dates.
Cheers, Damon

 

[zim_zimmer]

Yeah that was what I wanted to do in the first place, create a pivot
table but I've been told its got to be kept as a spreadsheet.

I could do it by inserting rows and copying them across but there are
about 10,000 rows so it would take forever!!


Don't know if a vlookup would be involved but I want to match the order
numbers and return the invoice dates :-S

 

[Roger Govier]

Hi

Maybe you could use Advanced Filter to extract the set of rows relating
to a given Order number to a second sheet.
For help on this function take a look at
http://www.contextures.com/xladvfilter01.html#ExtractWs

 

[RagDyeR]

This could be done using formulas, but, with the configuration you posted,
can you dedicate the number of columns to the right to display all possible
returns, one column for each possibility?
You're talking about 10 to 12 to 15 or so ... aren't you?

Post back if you're interested.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------

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*




Any ideas??