Need a vlookup that returns sum of values in month cells

chris pike
chris pike used Ask the Experts™
on
Need help with formula

Vlookup name and give me sum of 12 months
vlookup and sum
I can not add columns the vlookup will be on a different sheet
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Rob HensonFinance Analyst

Commented:
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:

Author

Commented:
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

Author

Commented:
Correct Rob
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Rob HensonFinance Analyst

Commented:
See attached with formula on sheet1:

=SUM(OFFSET(Chart!$D$4,MATCH($A$4,Chart!$A$4:$A$25,0)-1,0,1,12))
dummy-sheet-difference-in-months.xlsx

Author

Commented:
Vlook up name from column A the add up values in same row d through o
Rob HensonFinance Analyst

Commented:
Alternative using two vlookups:

=VLOOKUP(Sheet1!$A$4,Chart!$A$4:$Q$25,3,FALSE)-VLOOKUP(Sheet1!$A$4,Chart!$A$4:$Q$25,16,FALSE)

First vlookup gets Entitled hours from column C
Second vlookup gets balance from column P

Entitled minus sum of D to O equals Balance therefore
Entitled minus Balance equals sum of D to O
NorieAnalyst Assistant

Commented:
You could try INDEX/MATCH to get the range to sum.

Assuming the posted sheet is Sheet1 and on the other sheet you have the names starting in A2.

=SUM(INDEX(Sheet1!$D$4:$O$100, MATCH(A2, Sheet1!$A$4:$A$100, 0),))
Rob HensonFinance Analyst

Commented:
Depending on size of file and speed of calculation, may want to use less volatile function of LOOKUP

=LOOKUP($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)

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.

Author

Commented:
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??
Rob HensonFinance Analyst

Commented:
Can you upload the file with both sheets to demonstrate.

Author

Commented:
Ok working on stripping it
NorieAnalyst Assistant

Commented:
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.

Author

Commented:
Ok here we go lol
lookup-table-help.xlsx
Rob HensonFinance Analyst

Commented:
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?
NorieAnalyst Assistant

Commented:
Try this in O2 on the sheet 'Data' and copy down.

=SUM(INDEX(Chart!$D$4:$O$30,MATCH(LEFT([@[Production-0011]],SEARCH(" ",[@[Production-0011]])-1)&MID([@[Production-0011]], SEARCH(" ", [@[Production-0011]]),2), Chart!$A$4:$A$30,0),))

Or, slightly shorter.

=SUM(INDEX(Chart!$D$4:$O$30,MATCH(LEFT([@[Production-0011]],SEARCH(" ",[@[Production-0011]])+1), Chart!$A$4:$A$30,0),))

Author

Commented:
Norie still trying … no luck so far
NorieAnalyst Assistant
Commented:
See the attached workbook.
lookup-table-help-V2.xlsx

Author

Commented:
Rob Yours works great
Finance Analyst
Commented:
See attached, with four options on the Data sheet.
lookup-table-help.xlsx

Author

Commented:
Thank you for your help.
Thanks Rob for the multiple options to use different codes.
Rob HensonFinance Analyst

Commented:
Happy to help, glad we got there in the end.

Author

Commented:
Is it easy to divide the sum by 8 hours to give a day value??
Rob HensonFinance Analyst

Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial