Vlookup icw =product()

  • Thread starter Thread starter Robert
  • Start date Start date
R

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
 
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
|
 
=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)
 
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
 
Back
Top