Sum range with cell address of lookup result

[Dev Rao]

Dear All,

I need to sum of a range where the first cell is the result of lookup and after that i have to make a range for sum.

Like

if =Cell(Address,vlookup)
if the vlookup result value consist in A1 cell
Then cell formula will give result as $a$1

Now i have to sum of above given cell $a$1 and $a$2

like Sum(Cell(Address, vlookup result) : $a$2
Please help in the same.

 

[jasonb75]

You can't do it with lookup, but can with index and match, something on the basis of

=sum(index(lookup result range, match( lookup value, lookup match range, false)):$A$2)

If the result should always be the result of the vlookup plus the cell below it then you could use this more reliable method instead of a cell reference after index.

=sum(index(lookup result range, match( lookup value, lookup match range, false)+{0,1}))

 

[Alex Blakenburg]

You can't do it with lookup

With XLookup technically you can since XLookup returns a Cell, but you would need to wrap it in an offset function to achieve the same results as your last formula with the +{0,1}. With Offset being volatile, I think your last formula is much better.

=SUM(OFFSET(XLOOKUP(110,$A$6:$A$11,$A$6:$A$11),0,0,2))

 

[jasonb75]

but you would need to wrap it in an offset function to achieve the same results as your last formula

Actually, you can without offset

Note that the results of the 2 methods will be different, the first will give the result of =SUM(C2,C4) while the second will give =SUM(C2:C4)

=SUM(XLOOKUP(A11,A2:A8,CHOOSE({1,2},C2:C8,C4:C10),))

=SUM(XLOOKUP(A11,A2:A8,C2:C8,):XLOOKUP(A11,A2:A8,C4:C10,))