Re: Multiple Answers For A Look up

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: no spam (nospam_at_nospam.com)
Date: 08/09/04 

Date: Mon, 9 Aug 2004 22:57:55 +0100

Nope no joy! Won't this formula just give the lowest value?

Thanks anyway :o)

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:O3fVWolfEHA.632@TK2MSFTNGP12.phx.gbl...
> Hi
> try the array formula (entered with CTRL+SHIFT+ENTER):
> =INDEX(fgnpo!$A$1:$B$14001,SMALL(IF(fgnpo!$A$1:$A$14001=$M$2,ROW(fgnpo!
> $A$1:$A$14001)),ROW(1:1)))
>
> and copy down
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
>
> no spam wrote:
> > Yep thought about that but there are just too many orders and
> > receipts!
> >
> > Surely there must be a way of getting multiple values from this type
> > of lookup. I can't believe I'm the only person to have this problem?
> > Is there a way of finding the first , second, third etc instances,
> > because I was thinking using a countif function would tell how many
> > instances an order appeared in the list.
> >
> > thanks for the help any way I'll continue searching and report back
> > soon!
> >
> > No Spam :o)
> >
> >
> >
> > "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> > news:ekWS9HkfEHA.1652@TK2MSFTNGP09.phx.gbl...
> >> Hi
> >> why not use a filter for this? ('Data - filter - Advanced Filter)
> >>
> >> --
> >> Regards
> >> Frank Kabel
> >> Frankfurt, Germany
> >>
> >>
> >> NOSPAM wrote:
> >>> Help.
> >>>
> >>> I have two columns of data. One is a receipt number the other is
> an
> >>> order number. Basically, I want to create a lookup/index that
> will
> >>> show me all the order numbers associated to a receipt in one cell.
> >>> i.e. Each receipt can be for more than one order.
> >>>
> >>> I've got this so far.
> >>>
> >>> =INDEX(fgnpo!$A$2:$B$14001,MATCH(M2,fgnpo!$A$2:$A$14001,0),2)
> >>>
> >>> This will return one of the values, but not all of them.
> >>>
> >>> Here's part of the list.
> >>>
> >>> Receipt Order
> >>>
> >>>
> >>> 12668
> >>>
> >>> 26685
> >>> 12669 26615
> >>> 12670 26630
> >>> 12671 26145
> >>> 12671 26458
> >>> 12671 26591
> >>>
> >>>
> >>>
> >>> What I want is it to show
> >>>
> >>> Receipt Order
> >>>
> >>>
> >>> 12668 26685
> >>> 12669 26615
> >>> 12671 26630
> >>> 12671 26145, 26458, 26591.
> >>>
> >>>
> >>> Is this possible?
> >>>
> >>> Please help.
> >>>
> >>> Many thanks
> >>>
> >>> No spam! :o)
>

Relevant Pages