Using VLOOKUP in an array

[Robert A]

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))*(D510)

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

(e-mail address removed)

 

[Frank Kabel]

Hi
something like
=SUMPRODUCT(--(Returns=B36),C5:C10,D510)
not sure how 'Returns' is defined but VLOOKUP is definetly not the
correct function for this

 

[Robert A]

"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]

Hi Robert
I'm quite sure SUMPRODUCT is what you're looking for. If you may post
some example rows of your data (plain text) and describe your expected
result it's easy to derive a SUMPRODUCT formula

 

[Peo Sjoblom]

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

 

[Robert A]

Please see my reply in CAPS below

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?

 

[Frank Kabel]

Hi
still not very clear what you're trying to do
Please post some example rows of your data (plain text - no attachment
please) and describe based on these example data your expected result