Vlookup icw =product()

[Robert]

Hi,

Can someone tell me which formula to use for the following command: I
would like to lookup a certain name in another sheet and subsequently
take the PRODUCT of the range of the numbers stated after this name.
For example: find RNNF in sheet1 (column A)....say this is cell
A1....... and take =PRODUCT() of values in B1:Z1

I tried =VLOOKUP(RNNF,'Performance Funds'!
A1:A31,PRODUCT(1+'Performance Funds'!B:Z)-1,0) but no luck;-(


Thanks a lot!!!!

Rgds,
Robert

 

[Niek Otten]

Hi Robert,

=PRODUCT(INDIRECT("B"&MATCH("RNNF",A1:A3,0)&":Z"&MATCH("RNNF",A1:A3,0)))


You can put the MATCH("RNNF",A1:A3,0) part in a separate cell so it has to be evaluated only once

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| Can someone tell me which formula to use for the following command: I
| would like to lookup a certain name in another sheet and subsequently
| take the PRODUCT of the range of the numbers stated after this name.
| For example: find RNNF in sheet1 (column A)....say this is cell
| A1....... and take =PRODUCT() of values in B1:Z1
|
| I tried =VLOOKUP(RNNF,'Performance Funds'!
| A1:A31,PRODUCT(1+'Performance Funds'!B:Z)-1,0) but no luck;-(
|
|
| Thanks a lot!!!!
|
| Rgds,
| Robert
|

 

[Bob Phillips]

=PRODUCT(INDEX('Performance Funds'!1:31,MATCH(RNNF,'Performance
Funds'!A1:A31,0),2):INDEX('Performance Funds'!1:31,MATCH(RNNF,'Performance
Funds'!A1:A31,0),26))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Robert]

Hi Nick,

Thanks for the suggestion. Do you perhaps know how to make the range
(which contains the numbers) flexible?
To make it more clear

Sheet with results
31-1-2007 28-2-2007 31-3-2007
RNNF x


Source sheet containing performance numbers
A B C D
31-1-2007 28-2-2007 31-3-2007
RNNN
RNNE
RNNF

For "x" I want to calucate the cummulative performance of the period
31-1-2007 untill 28-2-2007 so the most desired way would be to make a
formula that looks at the date (i.e. 28-2-2007) and subsequently
determines the range (i.e. 31-1-2007 up to 28-2-2007)

Is there a way to cover this in one formula?

Thanks again!!!

Rgds,
Robert