Re: Using VLOOKUP in an array

From: Peo Sjoblom (terre08_at_mvps.org)
Date: 05/19/04 

Date: Tue, 18 May 2004 20:30:35 -0400

You realize that VLOOKUP can only look up one value in the left most column
of "Returns"?
So if we for simplicity assume that "Returns" is a table with the dimensions
of A1:J10
if so what you want is to sum all values of the row that the B36 will find,
so if B36 will match
A5 you want to sum A5:J5? If so you can use this array formula

=SUM(VLOOKUP(B36,Returns,ROW(INDIRECT("1:10")),0))

or

=SUM(VLOOKUP(B36,Returns,ROW(INDIRECT("1:10"))))

depending on whether you want an exact match or not

That would equal

=SUMPRODUCT((INDEX(Returns,,1)=B36)*(Returns))

entered normally 

-- 
Regards,
Peo Sjoblom
"Robert A" <robert.ades@verizon.net> wrote in message
news:2Wqqc.83187$sK3.62721@nwrddc03.gnilink.net...
> "Returns" is a data table for Vlookup.  I'm attempting to use an array so
> that I can do multiple lookups in the same formula.
>
> For instance, the Returns table has 10 columns, each column represents a
> different stock index.  The rows in the Returns table represent a series
of
> years.  My array formula looks up one or more indexes for a given year,
and
> multiplies the individual indexes by an associated percentage for that
index
> to produce a combined result..
>
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:OSkKsOPPEHA.3348@TK2MSFTNGP09.phx.gbl...
> > Hi
> > something like
> > =SUMPRODUCT(--(Returns=B36),C5:C10,D5:D10)
> > not sure how 'Returns' is defined but VLOOKUP is definetly not the
> > correct function for this
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> >
> > Robert A wrote:
> > > Group:
> > >
> > > When I use VLOOKUP in an array as follows, the formula returns only
> > > the result of C5 times (D5 through D10):
> > >
> > > =SUM(VLOOKUP(B36,Returns,C5:C10))*(D5:D10)
> > >
> > > I'm trying to get C5 times D5, C6 times D6, etc.
> > >
> > > I've been struggling with this for a week.  How can VLOOKUP be used
> > > properly in an array?
> > >
> > > Robert
> > >
> > > ___________________________
> > > Robert Ades Accountancy Corp.
> > > 12100 Wilshire Blvd., Suite 660
> > > Los Angeles, CA 90025
> > > Tel:  310-571-1224
> > > Fax:  310-571-1227
> > >
> > > robert@robertades.com
> >
>
>