excel match function used in a formula as a refrence

I have开发者_C百科 the following formula

=VLOOKUP(A4,Logged!A:I,3)+VLOOKUP(A4+1,Logged!A:I,3)+VLOOKUP(A4+3,Logged!A:I,3).....

As it is long formula I wanted to use the MATCH function

=MATCH(A4,Logged!A:A,0)

which gives me a result of 12.

now all I want to do is do some thing like

=SUM($e$(MATCH(A4,A:A,0):$E$MATCH(A4,A:A,0)+5)

which I was hoping would give

=SUM(E12:E17)

but I only get an error.

Your question is very confused, but from what I understood:
Build your reference in one cell, say X10:
="$e$" & MATCH(A4,A:A,0) & ":$E$" & MATCH(A4,A:A,0)+5
Make sure X10 evaluates to "$e$12:$e$17" After that, use =SUM(INDIRECT(X10))