Xlookup with duplicate Lookup Values

[Mayzy4186]

Hoping for some help with a Xlookup,

I have a table with data that is outlined like the below:

I have an Xlookup function which is successfully returning the first row to a separate sheet however it will only return this first row and not the subsequent different tasks, KPI measures, updates etc.

=XLOOKUP(Data!$AA$2,Table1[[#All],[PROJECT]],Table1[[#All],[KEY ACTION]:[RESPONSIBLE]])

I would like this to populate the different values in the other columns down based on the Lookup value in the Project column. Do I need a second Lookup function?

Thanks!

Strategic Planning Dashboard mockup.xlsx
	B	C	D	E	F	G
1	PROJECT	KEY ACTION	TASK	KPI TARGET- MEASURES OF SUCCESS	PROGRESS UPDATE	RESPONSIBLE
2	SAFETY LEADERSHIP	PLANNING	Develop a Safety Strategy Plan	3 year planning framework , signed off MD	ST to provide Safety Strategy template	HSE Committee - Dave - Jess, Lorelle
3	SAFETY LEADERSHIP	PLANNING	Develop and implement a Training Plan	Plan to be costed, communicated and scheduled	Training & Needs Analysis statement to be developed- Dec 22 (Consider historical data)	HSE Committee - Dave - Jess, Lorelle
4	SAFETY LEADERSHIP	COMPLIANCE	Rolling compliance	Included within Training Plan		HSE Committee - Dave - Jess, Lorelle
5	SAFETY LEADERSHIP	COMPLIANCE	Hold formal audits – Twice Yearly	Scheduled implemented	Desk top audit & site audit. Schedule to be developed by Oct 22	HSE Committee - Dave - Jess, Lorelle
Data

Strategic Planning Dashboard mockup.xlsx
	Z	AA	AB
1
2		SAFETY LEADERSHIP
3		PEOPLE AND CULTURE
4		SALES AND MARKETING
5		OPERATIONS
6		INFORMATION TECHNOLOGY
7		FINANCE AND COMMERICAL
8
Data

Strategic Planning Dashboard mockup.xlsx
	A	B	C	D	E	F
1	XXXX
2	SAFETY LEADERSHIP
3	KEY ACTION	TASK	KPI TARGET- MEASURES OF SUCCESS	PROGRESS UPDATE	RESPONSIBLE
4	PLANNING	Develop a Safety Strategy Plan	3 year planning framework , signed off MD	ST to provide Safety Strategy template	HSE Committee - Dave - Jess, Lorelle
5	PLANNING	Develop a Safety Strategy Plan	3 year planning framework , signed off MD	ST to provide Safety Strategy template	HSE Committee - Dave - Jess, Lorelle
6	PLANNING	Develop a Safety Strategy Plan	3 year planning framework , signed off MD	ST to provide Safety Strategy template	HSE Committee - Dave - Jess, Lorelle
7	PLANNING	Develop a Safety Strategy Plan	3 year planning framework , signed off MD	ST to provide Safety Strategy template	HSE Committee - Dave - Jess, Lorelle
8	PLANNING	Develop a Safety Strategy Plan	3 year planning framework , signed off MD	ST to provide Safety Strategy template	HSE Committee - Dave - Jess, Lorelle
9	PLANNING	Develop a Safety Strategy Plan	3 year planning framework , signed off MD	ST to provide Safety Strategy template	HSE Committee - Dave - Jess, Lorelle
SAFETY

Cell Formulas
Range	Formula
A4:E5	A4	=XLOOKUP(Data!$AA$2,Table1[[#All],[PROJECT]],Table1[[#All],[KEY ACTION]:[RESPONSIBLE]])
A6:E9	A6	=XLOOKUP(Data!AA$2,Table1[[#All],[PROJECT]],Table1[[#All],[KEY ACTION]:[RESPONSIBLE]])
Dynamic array formulas.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=filter(Table1[[#All],[KEY ACTION]:[RESPONSIBLE]],Data!$AA$2=Table1[[#All],[PROJECT]])

 

[Mayzy4186]

Worked a treat, thanks very much.

 

[Fluff]

Glad to help & thanks for the feedback.