INDEX MATCH or VLOOKUP Multiple Criteria

[Robby19]

I'm thinking this might have to be an array of some sort, but here goes nothing. I have a tab that pulls 2 different codes, TA and TZ. Each tab uses EDIPI (individual unique identifier). I want to match in 4 separate columns the following; TA Code, TA Code Date, TZ Code, TZ Code Date

I created this =INDEX(TRS_DATA[Training Event Code],MATCH(1,([@EDIPI]=TRS_DATA[EDIPI])*(TRS_DATA[@Form]=FTAPBEGPOP[[#Headers],[TA]]),0)) but it doesn't work at all.

Can anyone help with the correct formula?

Sheet 1 contains the basic date, Sheet 2 would contain the formulas pulling from Sheet 1.

Sheet 2
[TABLE="width: 500"]
<tbody>[TR]
[TD]EDIPI[/TD]
[TD]TA[/TD]
[TD]DATE[/TD]
[TD]TZ[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]TA[/TD]
[TD]21 Apr 2018[/TD]
[TD]TZ[/TD]
[TD]22 Apr 2018[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]TA[/TD]
[TD]19 Apr 2018[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet 1[TABLE="width: 500"]
<tbody>[TR]
[TD]EDIPI[/TD]
[TD]CODE[/TD]
[TD]DATE[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]TA[/TD]
[TD]21 Apr 2018[/TD]
[/TR]
[TR]
[TD]124[/TD]
[TD]TA[/TD]
[TD]19 Apr 2018[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]TZ[/TD]
[TD]22 Apr 2018[/TD]
[/TR]
[TR]
[TD]125[/TD]
[TD]TZ[/TD]
[TD]7 Apr 2018[/TD]
[/TR]
[TR]
[TD]126[/TD]
[TD]TA[/TD]
[TD]18 Apr 2018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Sheet1, i.e. TRS_DATA, is a 3-column table (input). But it's not clear what must happen in Sheet2. Care to post what the output must be, without mentioning any formula?

 

[Robby19]

You are correct, sheet 1 is just a data pull from our system that shows who completed which class and when. The object of Sheet 2 is to combine both versions of the class, codes TA and TZ, and show them on one line for each individual. Sheet 2 also pulls compliance information and other pertinent date from the rest of the spreadsheet. The desired output for sheet 2 is to be a by-name dashboard of sorts.

 

[Aladin Akyurek]

So, we move from one representation (Sheet1) to another (Sheet2).

Care to indicate what is given in Sheet2 and what must be computed?

 

[Robby19]

So I think I figured it out. I created a pivot table and filtered only the TZ data into it, then used the GETPIVOTDATA formula. I appreciate the responses!

 

[Aladin Akyurek]

So I think I figured it out. I created a pivot table and filtered only the TZ data into it, then used the GETPIVOTDATA formula. I appreciate the responses!

That's a good idea. Otherwise, we had to create the output as a whole by means of formulas (not impossible though).