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

EDIPI	TA	DATE	TZ	DATE
123	TA	21 Apr 2018	TZ	22 Apr 2018
124	TA	19 Apr 2018

<tbody>
</tbody>

Sheet 1

EDIPI	CODE	DATE
123	TA	21 Apr 2018
124	TA	19 Apr 2018
123	TZ	22 Apr 2018
125	TZ	7 Apr 2018
126	TA	18 Apr 2018

<tbody>
</tbody>

 

[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).