Lookup and add the value of the column next to it to the sum

[huy_le]

Hi guys,

I am trying to find value for a particular date and then sum this value with the value from the column next to it.

I can use hlookup to get the first value but don't know how to include the value of the next column to it.

Please help. Thanks.

[Pauleyb]

Try:
=SUM(OFFSET($A$2,0,MATCH(E2,$A$1:$B$1,0)-2,1,2))

[FDibbins]

What is the basis of finding the next column to add? Perhaps sumifs() might help?

[huy_le]

Pauleyb, your formula works, but assuming I have a large set of data, I can't go to each one to choose the cell like you did. I don't know what date it will be to choose A2 like you did. It will not always be a2.

[huy_le]

The example I sent was not good, so I remade it. Hopefully it will help clarify it a bit more.

Thanks guys!

[huy_le]

The basis is that I have a date and a range of dates with values. I want to find the value of a particular date and sum it with the value of the next columns to get the total spending. Is that clear enough?

[Pauleyb]

I think my formula was still essentially correct. I just had to expand it for your new dates and change the reference point. Put this in I7 and then drag down to I8
=SUM(OFFSET($A$2,0,MATCH(H7,$A$1:$E$1,0)-2,1,2))

[FDibbins]

You didnt specify which side, but it looks like the cell to the left? TRy this....
=INDEX($A2:$E2,MATCH($H$7,$A$1:$E$1,0))+INDEX($A2:$E2,MATCH($H$7,$A$1:$E$1,0)-1)
If yoy want it to take the cell on the right, chanfge it to this...
=INDEX($A2:$E2,MATCH($H$7,$A$1:$E$1,0))+INDEX($A2:$E2,MATCH($H$7,$A$1:$E$1,0)+1)

[huy_le]

Hey pauleyb,

Thanks! It works! Perfect man! Appreciate it very much!

[FDibbins]

Happy to help and thanks for the feedback