Re: Using VLOOKUP in an array
From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 05/19/04
- Next message: Gargamil: "Linking to Paradox"
- Previous message: Frank Kabel: "Re: Help...."
- In reply to: Robert A: "Re: Using VLOOKUP in an array"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 19 May 2004 15:54:10 +0200
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
--
Regards
Frank Kabel
Frankfurt, Germany
Robert A wrote:
> 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
- Next message: Gargamil: "Linking to Paradox"
- Previous message: Frank Kabel: "Re: Help...."
- In reply to: Robert A: "Re: Using VLOOKUP in an array"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
