to sum the data match in two files by using Vlookup

miss Niki

New Member
Joined
Oct 22, 2013
Messages
9
Hi all..

i have two excel files which are data and master..both files will be use for salary calculating.. the vlookup formula will be use in master files for dragging the salary data from Data files..the formula as follow
VLOOKUP(B4:B225,'D:\Salary\[Data.xls]AUG'!A$1:F$65536,6,FALSE))

the vlookup working fine to me.. but my problems is i want the data to be auto calculated when they have same value in two columns.. or if the name is similar/match in two columns (one for salary and another one for overtime), the salary should be auto calculated.

Data files will contain of these:
a:Employee ID
b:employee name
c:Employee salary/Overtime
[TABLE="width: 516"]
<TBODY>[TR]
[TD]</SPAN>Master files will contain of these:
a:Employee ID
b:Emplyee Name
c:Employee Nett Salary (that will be dragging from Data files)

is there any formula that i can used to combined with my vlookup formula?

appreciate any help! thanks!!:eeek::eeek:
[TABLE="width: 516"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
[TABLE="width: 516"]
<TBODY>[TR]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
I don't really understand your VLOOKUP formula. You appear to be looking up an array inside of another array. What are you looking up and what is it returning?
 
Upvote 0
Hi Ben..appreciate your respond.. actually i wanted to copy match data from two different files by using VLOOKUP..

pasted here is the formula that i used for VLOOKUP..
=VLOOKUP(A:A,'D:\Salary\[Data.xls]Sept2013'!A$1:D$65536,4,FALSE)

1. A:A :Staff ID (From Master File)
2.'D:\Salary\[Data.xls]Sept2013'!A$1:D$65536 : Range for the table array (From Data File in Salary Folder)
3. 4: Staff Salary in D column (From Data File)

sometimes, staff working over time..so there is another column For overtime staff in Data Files.. which means got 2 columns for one staf..one for salary and one for overtime.. the table as below;

Master.xls
[TABLE="width: 349"]
<TBODY>[TR]
[TD]Staff ID</SPAN>
[/TD]
[TD]Staff Name</SPAN>
[/TD]
[TD]Nett Salary</SPAN>
[/TD]
[/TR]
[TR]
[TD]E002108</SPAN>
[/TD]
[TD]Nichole</SPAN>
[/TD]
[TD]618
[/TD]
[/TR]
[TR]
[TD]E002366</SPAN>
[/TD]
[TD]Benito</SPAN>
[/TD]
[TD="align: right"]202</SPAN>
[/TD]
[/TR]
[TR]
[TD]E002368</SPAN>
[/TD]
[TD]***ashi</SPAN>
[/TD]
[TD="align: right"]3300</SPAN>
[/TD]
[/TR]
[TR]
[TD]E002395</SPAN>
[/TD]
[TD]Takahiko</SPAN>
[/TD]
[TD="align: right"]2299</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]

As for nicole and benito, they have done overtime..so the nett salary should be Overtime + Salary...

Data.Xls

[TABLE="width: 465"]
<TBODY>[TR]
[TD]Staff ID</SPAN>[/TD]
[TD]Staff Name</SPAN>[/TD]
[TD]Salary/Over Time</SPAN>[/TD]
[TD]Salary</SPAN>[/TD]
[/TR]
[TR]
[TD]E002108</SPAN>[/TD]
[TD]Nichole</SPAN>[/TD]
[TD]Over Time</SPAN>[/TD]
[TD]618</SPAN>[/TD]
[/TR]
[TR]
[TD]E002366</SPAN>[/TD]
[TD]Benito</SPAN>[/TD]
[TD]OverTime</SPAN>[/TD]
[TD]202</SPAN>[/TD]
[/TR]
[TR]
[TD]E002368</SPAN>[/TD]
[TD]***ashi</SPAN>[/TD]
[TD]Salary</SPAN>[/TD]
[TD]3,300</SPAN>[/TD]
[/TR]
[TR]
[TD]E002561</SPAN>[/TD]
[TD]Yoshiro</SPAN>[/TD]
[TD]Salary</SPAN>[/TD]
[TD]1,350</SPAN>[/TD]
[/TR]
[TR]
[TD]E002565</SPAN>[/TD]
[TD]Yumi</SPAN>[/TD]
[TD]Salary</SPAN>[/TD]
[TD]3,620</SPAN>[/TD]
[/TR]
[TR]
[TD]E002108</SPAN>[/TD]
[TD]Nichole</SPAN>[/TD]
[TD]Salary</SPAN>[/TD]
[TD]3,200</SPAN>[/TD]
[/TR]
[TR]
[TD]E002366</SPAN>[/TD]
[TD]Benito</SPAN>[/TD]
[TD]Salary</SPAN>[/TD]
[TD="align: right"]3,320</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL></COLGROUP>[/TABLE]
its been 2 days im searching for the formula..glad if u can help me out.. :'(
:rolleyes:
 
Upvote 0
I hope i can find vlookup formula that combined with countif or Sum formula that can calculate the overtime and Salary for Nichole and benito to be in Nett Salary..
 
Upvote 0
I think I understand... So you want a formula for the column Nett Salary (sic) that will sum all of the salary amounts for each employee (from workbook Data.xls, column - Salary?

To confirm, for Nichole, the result should be $3,818, for Benito, $3,522, for ***ashi, $3,300.

Is this correct?
 
Upvote 0
If that's what you are looking for, than perhaps this?

Excel 2010
ABC
Staff IDStaff NameNett Salary
E002108Nichole
E002366Benito
E002368***ashi

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]3818[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]3522[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3300[/TD]

</tbody>
Master File

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=SUMIF('D:\Salary\[Data.xls]Sept2013'!$B$2:$B$65536,B2,'D:\Salary\[Data.xls]Sept2013'!$D$2:$D$65536)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Thank you for helping me out..really appreciate that..

the formula seems great..but the result that i get is #value! error..is there anything that i need to do other than the formula?

thank you!
 
Upvote 0
I'm not sure it will work if the Data workbook is closed. Try opening the workbook, and see if the formula calculates.
 
Upvote 0
To reference the workbook without opening it, try this array formula instead:

Note: You must confirm this formula with Ctrl+Shift+Enter instead of just enter.

=SUM(IF('D:\Salary\[Data.xls]Sept2013'!$B$2:$B$65536=B2,'D:\Salary\[Data.xls]Sept2013'!$D$2:$D$65536))
 
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,265
Members
452,627
Latest member
KitkatToby

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top