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.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]Application[/TD]
[TD]total Scans[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD]beltsplit[/TD]
[TD]12500[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]1234[/TD]
[TD]primarysort[/TD]
[TD]1300[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]1234[/TD]
[TD]vpls[/TD]
[TD]4000[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]2345[/TD]
[TD]primarysort[/TD]
[TD]3000[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]2345[/TD]
[TD]vpls[/TD]
[TD]1200[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]3456[/TD]
[TD]beltsplit[/TD]
[TD]12000[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]3456[/TD]
[TD]vpls[/TD]
[TD]4200[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]3456[/TD]
[TD]primarysort[/TD]
[TD]2300[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]ID[/TD]
[TD]beltsplit[/TD]
[TD]primarysort[/TD]
[TD]vpls[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1234[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2345[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3456[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]ID[/TD]
[TD]Application[/TD]
[TD]total Scans[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1234​

[/TD]
[TD]beltsplit[/TD]
[TD]

12500​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

1234​

[/TD]
[TD]primarysort[/TD]
[TD]

1300​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

1234​

[/TD]
[TD]vpls[/TD]
[TD]

4000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

2345​

[/TD]
[TD]primarysort[/TD]
[TD]

3000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]

2345​

[/TD]
[TD]vpls[/TD]
[TD]

1200​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]

3456​

[/TD]
[TD]beltsplit[/TD]
[TD]

12000​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD]

3456​

[/TD]
[TD]vpls[/TD]
[TD]

4200​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]

3456​

[/TD]
[TD]primarysort[/TD]
[TD]

2300​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]ID[/TD]
[TD]beltsplit[/TD]
[TD]primarysort[/TD]
[TD]vpls[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]

1234​

[/TD]
[TD]

12500​

[/TD]
[TD]

1300​

[/TD]
[TD]

4000​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]

2345​

[/TD]
[TD]

0​

[/TD]
[TD]

3000​

[/TD]
[TD]

1200​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]

3456​

[/TD]
[TD]

12000​

[/TD]
[TD]

2300​

[/TD]
[TD]

4200​

[/TD]
[/TR]
</tbody>[/TABLE]


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.