Need a vlookup that returns sum of values in month cells
Need help with formula
Vlookup name and give me sum of 12 months
I can not add columns the vlookup will be on a different sheet
Microsoft ExcelMicrosoft Office
Last Comment
Rob Henson
8/22/2022 - Mon
Rob Henson
As always, can you upload a sample file to describe what you need.
Think you might be able to use OFFSET, I am assuming you want to add up columns D to O for a name chosen from column A:
chris pike
ASKER
I have macro that will sort the names in column a
If I add a column to the end it wont follow, it's VBA .
I would just like a simple formula to vlook and add the values
Thanks dummy-sheet-difference-in-months.xlsx
Whereas VLOOKUP uses the whole data area, LOOKUP only uses the lookup column and the result column(s). This is less volatile in the sense that the VLOOKUP will be forced to recalculate whenever anything in the range A to P changes, whereas LOOKUP will be forced to calculate only when changes occur in columns A, C or P; as it happens changes in P will be as a result of changes in columns C and D to O so probably not that different in calculation time.
chris pike
ASKER
OK Rob your formula works, if I am looking up on the same sheet.
I will be looking FROM a different sheet into THIS Sheet
Same workbook , but different sheet
I tried putting the Chart! infront to the lookup
=LOOKUP(Chart!$A$4,Chart!$A$4:$A$100,Chart!$C$4:$C$100)-LOOKUP($A$4,Chart!$A$4:$A$100,Chart!$P$4:$P$100)
And there will be spaces in column A , will that be a problem??
Can you upload the file with both sheets to demonstrate.
chris pike
ASKER
Ok working on stripping it
Norie
The formula I posted should work when on another sheet, you only need to change the sheet name 'Sheet1' to reflect the name of the sheet from your original post.
Immediate issue when first looking, the data on Chart has names such as Anita D but Data sheet has Anita Dhanda.
.
Will these be consistent? Which will it be so we can change to be consistent?
Is it easy to divide the sum by 8 hours to give a day value??
Rob Henson
For the SUM(OFFSET(...)) and SUM(INDEX(...)) formulas Just add /8 on the end of the formula. For the lookup style formulas you will have to enclose the vlookup-vlookup in brackets before dividing by 8 to get the order of calculation correct, ie (VLOOKUP(...) - VLOOKUP(...))/8
Think you might be able to use OFFSET, I am assuming you want to add up columns D to O for a name chosen from column A: