Re: Using VLOOKUP in an array

From: Robert A (la_native_at_hotmail.com)
Date: 05/19/04 

Date: Tue, 18 May 2004 22:11:00 -0700

Please see my reply in CAPS below

"Peo Sjoblom" <terre08@mvps.org> wrote in message
news:uHHr$hTPEHA.2976@TK2MSFTNGP10.phx.gbl...
> 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,

I WOULD LIKE TO SUM THE PRODUCT OF (THE VALUES B31 WILL FIND TIMES THE
PERCENTAGES IN CELLS D5 THROUGH D10)

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

I THOUGHT THE "ROW(INDIRECT)" SHOULD BE "COLUMN(INDIRECT)", NO?
>
> 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
> > >
> >
> >
>
>

Loading