Search code examples
excelexcel-formulaexcel-match

excel match function used in a formula as a refrence

I have 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.

Solution

  • 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))