Liz has attended:
SharePoint course
Excel formula
I have a spreadsheet in which rows 24 to 168 have types identified in row D and attributes/values relating to each type in the other columns to the right of D.
Below this is another zone which has locations identified on each row, and for each location a number of types are entered in cells G to O (max no. of types is 9)
I need a formula which will look up the relevant types which occur G to O, then look for the relevant type rows above and find the value of cell AS and add together all (i.e. up to 9 values)
Can you please help?
RE: Excel formula
Hi Liz,
Thanks for the forum question.
I have attached a workbook where you in sheet1 cell B5 can see my solution.
I am using a sumproduct and vlookup functions.
If you change the value (1 to 5) in A5 you can see that Excel add up in B5.
In the vlookup I refer to an array by using curly brackets {}. The array I refer to is {2,3,4,5,6,7,8,9,10} column 2 to 10 in the vlookup array.
I hope this will do what you want.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
sum lookup.xlsx
