Reverse lookup resulting in a list of answers

[Montanes]

Hi,

I apologise in advance for my ignorance but I have been trying to get this to work for hours now and i am begging you to help before I check the flight capability of my laptop!

I have a tab called "EXPORT" which has the following details listed.

In an additional tab called "EMAIL", I am hoping to be able to enter a department in cell A1 which would then create a list of all rows with that department in column L of the 'EXPORT' tab as shown below:

The problem is the reverse lookup AND that any one department can be included in column L

'EXPORT': I don't expect anymore than 40 rows of data and I cant change the column order
'EMAIL': Anything can be changed in this tab except the fact I need the information of all 4 headers. It can be in any order and I wouldn't expect a result of anymore than 20 rows.

Apologies again for my very basic knowledge of formula and I would thank-you in advance for your assistance.

 

[Peter_SSs]

Try like these, copied down

Montanes.xlsm
	A	B	C	D	E
1	SALES DEPARTMENT
2
3	Date Due	Time Due	Fleet No	Job Title	Class Code of Vehicle
4	19/09/2022	08:00	UK22 WHY	SCHEDULED INSPECTION	MUSTANG MACH E
5	23/09/2022	14:00	GL43BEG	LOLER TEST	LAMBO GALLARDO
6
Email

Cell Formulas
Range	Formula
A4:A6	A4	=IFERROR(INDEX(Export!F:F,AGGREGATE(15,6,ROW(Export!F$2:F$100)/(Export!K$2:K$100=A$1),ROWS(A$4:A4))),"")
B4:B6	B4	=IFERROR(INDEX(Export!G:G,AGGREGATE(15,6,ROW(Export!G$2:G$100)/(Export!K$2:K$100=A$1),ROWS(B$4:B4))),"")
C4:C6	C4	=IFERROR(INDEX(Export!B:B,AGGREGATE(15,6,ROW(Export!B$2:B$100)/(Export!K$2:K$100=A$1),ROWS(C$4:C4))),"")
D4:D6	D4	=IFERROR(INDEX(Export!E:E,AGGREGATE(15,6,ROW(Export!E$2:E$100)/(Export!K$2:K$100=A$1),ROWS(D$4:D4))),"")
E4:E6	E4	=IFERROR(INDEX(Export!M:M,AGGREGATE(15,6,ROW(Export!M$2:M$100)/(Export!K$2:K$100=A$1),ROWS(E$4:E4))),"")

 

[Peter_SSs]

You could actually make the formulas much simpler & reduce the amount of calculations required if you used a helper column (which could be hidden after entering its formulas if you want)

Montanes.xlsm
	A	B	C	D	E	F	G
1	SALES DEPARTMENT
2
3	Date Due	Time Due	Fleet No	Job Title	Class Code of Vehicle
4	19/09/2022	08:00	UK22 WHY	SCHEDULED INSPECTION	MUSTANG MACH E		2
5	23/09/2022	14:00	GL43BEG	LOLER TEST	LAMBO GALLARDO		4
6
7
Email

Cell Formulas
Range	Formula
A4:A7	A4	=IF(G4="","",INDEX(Export!F:F,G4))
B4:B7	B4	=IF(G4="","",INDEX(Export!G:G,G4))
C4:C7	C4	=IF(G4="","",INDEX(Export!B:B,G4))
D4:D7	D4	=IF(G4="","",INDEX(Export!E:E,G4))
E4:E7	E4	=IF(G4="","",INDEX(Export!M:M,G4))
G4:G7	G4	=IFERROR(AGGREGATE(15,6,ROW(Export!K$2:K$100)/(Export!K$2:K$100=A$1),ROWS(G$4:G4)),"")