Using CELL to form the range in PRODUCT

[Alpha1980]

I need to use the following two formulas

=CELL("Address",INDEX(A1:A10,MATCH(B1,A1:A10,1)))
=CELL("Address",INDEX(A1:A10,MATCH(B2,A1:A10,1)))

to form the range in the following formula:

=PRODUCT(OFFSET(B1,0,1):OFFSET(B2,0,1))

e.g.

=PRODUCT(OFFSET(CELL("Address",INDEX(A1:A10,MATCH(B1,A1:A10,1))),0,1):OFFSET(CELL("Address",INDEX(A1:A10,MATCH(B2,A1:A10,1))),0,1))

However, Excel is telling me that this formula is returning an error.

Can anybody advise how to get around this? Thanks.

 

[MrKowz]

INDEX actually returns a range reference, so there is no need to use CELL.

Try:

=PRODUCT(OFFSET(INDEX(A1:A10,MATCH(B1,A1:A10,1)),0,1):OFFSET(INDEX(A1:A10,MATCH(B2,A1:A10,1)),0,1))

 

[barry houdini]

I'm not really clear what you are trying to do, aren't your reference values in B1 and B2 but the formula also seems to be returning the PRODCT range from that column, is that right?

If MrKowz is right then you can also eliminate OFFSET, try

=PRODUCT(INDEX(B1:B10,MATCH(B1,A1:A10,1)):INDEX(B1:B10,MATCH(B2,A1:A10,1)))

 

[Alpha1980]

Thanks guys.

I started with MrKowz solution and then figured I could indeed remove OFFSET as you suggest, Barry.