Help with Vlookup plus adding a user-input number of adjacent cells

[rham243]

So in the attached spreadsheet I have a Vlookup formula in cell F5 that works quite well based on the given input in the orange cells to the left.

I need to calculate the total cost in cell G5.

Currently I have C4 * F4 to calculate this however this is not right. I need to lookup the year in C3 and add the subsequent years (however many the user inputs in C4).

For example if someone inputs Cambridgeshire, 2019, and 3 years. The total cost in cell G4 should add up the cost for years 2019+2020+2021 to get the total cost of 135,087 NOT 131,113

This needs to work for any number of input years so if someone inputs 10 years, it will add up the start year plus the next 9.

Thanks for your help!

[zbor]

Here, try this:

Please Login or Register  to view this content.

[Pete_UK]

Use this array* formula in G5:

=SUM(IF(ROW(A1:A10)<=C4,VLOOKUP(C2,'Residential Care Annualy'!A2:AA59,MATCH(C3+ROW(A1:A10)-1,'Residential Care Annualy'!A1:AA1,0),FALSE)))

*An array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>.

Hope this helps.

Pete