Avatar of chris pike
chris pike
Flag for Canada asked on

Need a vlookup that returns sum of values in month cells

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
Microsoft ExcelMicrosoft Office

Avatar of undefined
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
chris pike

ASKER
Correct Rob
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Rob Henson

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
chris pike

ASKER
Vlook up name from column A the add up values in same row d through o
Rob Henson

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Norie

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 Henson

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.
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??
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Rob Henson

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
chris pike

ASKER
Ok here we go lol
lookup-table-help.xlsx
Rob Henson

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?
Norie

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),))
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
chris pike

ASKER
Norie still trying … no luck so far
SOLUTION
Norie

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
chris pike

ASKER
Rob Yours works great
ASKER CERTIFIED SOLUTION
Rob Henson

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
chris pike

ASKER
Thank you for your help.
Thanks Rob for the multiple options to use different codes.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

Happy to help, glad we got there in the end.
chris pike

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck