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

[paul_mittoo@hotmail.com]

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??

[Damon]

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


"paul_mittoo@hotmail.com" wrote:

> 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??
>
>

[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





Damon wrote:

> 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
>
>
> "paul_mittoo@hotmail.com" wrote:
>
> > 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??
> >
> >

[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

--
Regards

Roger Govier


"zim_zimmer" <paul_mittoo@hotmail.com> wrote in message
news:1152264501.273216.149850@b28g2000cwb.googlegroups.com...
> 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
>
>
>
>
>
> Damon wrote:
>
>> 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
>>
>>
>> "paul_mittoo@hotmail.com" wrote:
>>
>> > 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??
>> >
>> >
>

[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 !
----------------------------------------------------------------------------
-------------------

<paul_mittoo@hotmail.com> wrote in message
news:1152262464.934475.138640@k73g2000cwa.googlegroups.com...
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??