chris pike

asked on

# Need a vlookup that returns sum of values in month cells

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

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

ASKER

Correct Rob

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

=SUM(OFFSET(Chart!$D$4,MAT

dummy-sheet-difference-in-months.xlsx

ASKER

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

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

=VLOOKUP(Sheet1!$A$4,Chart

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

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),))

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

=SUM(INDEX(Sheet1!$D$4:$O$

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.

=LOOKUP($A$4,Chart!$A$4:$A

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.

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(rt!$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??

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,ChaAnd there will be spaces in column A , will that be a problem??

Can you upload the file with both sheets to demonstrate.

ASKER

Ok working on stripping it

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.

ASKER

Ok here we go lol

lookup-table-help.xlsx

lookup-table-help.xlsx

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?

.

Will these be consistent? Which will it be so we can change to be consistent?

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),))

=SUM(INDEX(Chart!$D$4:$O$3

Or, slightly shorter.

=SUM(INDEX(Chart!$D$4:$O$3

ASKER

Norie still trying … no luck so far

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Rob Yours works great

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thank you for your help.

Thanks Rob for the multiple options to use different codes.

Thanks Rob for the multiple options to use different codes.

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

ASKER

Is it easy to divide the sum by 8 hours to give a day value??

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: