vlookup index match difficulties

[brunetti92]

So i am trying to create and evaluation sheet for my employees. The first screen shot is all the necessary data that i need to pull from.
The second screen shot is where i want to put all my data to I can then use it for my evaluation sheet.
I enter the employee name is the first column which gets me the ID number and hours worked for that day. I then want the total scans in column G from the first screen shot to be entered in the second screen shot under the respective application. So it should search the ID numbers, then the application mode, and then print the total scans for that ID number and application in the second table. This is where I am having trouble. Ive tried vlookup along with if statements but i ran into the problem with vlookup when an employee has done more than one task. Ive also tried index match but I am getting stuck in lengthy equations.
Any help would be greatly appreciated.

 

[brunetti92]

I realized i can not post screen shots (whoops). So I have created sample tables.
I would like the "total scans" to be printed in the second table for the respective application and ID number.

	A	B	C
1	ID	Application	total Scans
2	1234	beltsplit	12500
3	1234	primarysort	1300
4	1234	vpls	4000
5	2345	primarysort	3000
6	2345	vpls	1200
7	3456	beltsplit	12000
8	3456	vpls	4200
9	3456	primarysort	2300

<tbody>
</tbody>

	A	B	C	D
1	ID	beltsplit	primarysort	vpls
2	1234
3	2345
4	3456

<tbody>
</tbody>

 

[dUBBINS]

I do not have time to write it myself right now. However, let me ask this question for whomever else stumbles in. Is VBA code okay to solve your problem?

 

[Aladin Akyurek]

Let A:C of Sheet1 house the input table and A:D of Sheet2 the output table.

In B2 of Sheet2 control+shift+enter, not just enter, copy across, and down:

=IFNA(INDEX(Sheet1!$C$2:$C$9,MATCH($A2,IF(Sheet1!$B$2:$B$9=B$1,Sheet1!$A$2:$A$9),0)),"")

If you get a #NAME? error, replace IFNA with IFERROR.

 

[MARZIOTULLIO]

	A​	B​	C​	D​
1​	ID	Application	total Scans
2​	1234​	beltsplit	12500​
3​	1234​	primarysort	1300​
4​	1234​	vpls	4000​
5​	2345​	primarysort	3000​
6​	2345​	vpls	1200​
7​	3456​	beltsplit	12000​
8​	3456​	vpls	4200​
9​	3456​	primarysort	2300​
10​
11​	ID	beltsplit	primarysort	vpls
12​	1234​	12500​	1300​	4000​
13​	2345​	0​	3000​	1200​
14​	3456​	12000​	2300​	4200​

<tbody>
</tbody>

B12=SUMIFS($C$2:$C$9,$A$2:$A$9,$A12,$B$2:$B$9,B$11) copy across and down

 

[dUBBINS]

Aladin...I was just trying both of your formulas to learn new things by deconstructing them. However, I cannot get your formula to work. It is basically just copying the information. But if there are duplicates, say two for customer 1234 buying a beltsplit scan, it doesn't add the total scans together. Maybe this isn't what brunette is looking for anyway. But if it isn't I do not see what would be different from one table to the next except for layout.

Either way, I learned something new from you and Marzio...so much appreciated. Very clever gents

 

[Aladin Akyurek]

@dUBBINS

The INDEX suggestion assumes that the OP wants to retrieve data. The input data seems to confirm this. But if it's summarization what is required, then the formula with SUMIFS is of course appropriate to that task.