Two-way lookup with dates (extract date based on person and visit)

[Jebboh]

Hey all,

I have an issue with my excel sheet where I am trying to extract a date from a calendar based on specific persons correct participation/visit. Essentially I have two tables: first one is a schedule where different participants are listed and everytime when they participate, one selects the occasion from a drop-down menu to track the progress and dates. The second table is on another sheet and is meant to further include the details of each participation and would be later used as source for pivot tables and graphs.

I've been trying two-way/nested xlookups and index/match methods to get the date of the occasion into the second table from the first based on person name and occasion code, however, I am really starting to run out of ideas now. Below a mock-up screenshot of the two tables. All help much appreciated!

 

[Fluff]

Could a person have the same Occasion code multiple times?

 

[Jebboh]

Could a person have the same Occasion code multiple times?

Hey,

In this specific case they're meant to be individual and only occur once per person.

Best,
Jebboh

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1		01/01/2022	02/01/2022	03/01/2022	04/01/2022	05/01/2022	06/01/2022	07/01/2022
2	Amy	P1			P2	P3
3	Ben			P1
4	Carrie						P1
5	David
6
7
8
9
10
11	Amy	P1	01/01/2022
12	Ben	P1	03/01/2022
13	Amy	P2	04/01/2022
14	Amy	P3	05/01/2022
15	Carrie	P1	06/01/2022
16
Data

Cell Formulas
Range	Formula
C11:C15	C11	=FILTER($B$1:$H$1,MMULT(SEQUENCE(,ROWS($A$2:$A$5),,0),($A$2:$A$5=A11)*($B$2:$H$5=B11))>0)