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.

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Montanes]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi, I have updated my account.

I use 365 on windows.

Ty

 

[Fluff]

Thanks for that.
How about

=LET(f,FILTER(Export!B2:G100,Export!L2:L100=A1),INDEX(f,SEQUENCE(ROWS(f)),{5,6,1,4}))

 

[Montanes]

Thanks for that.
How about

=LET(f,FILTER(Export!B2:G100,Export!L2:L100=A1),INDEX(f,SEQUENCE(ROWS(f)),{5,6,1,4}))

That's great thanks.

Just out of curiosity, what does the "f" mean? I am trying to understand the formula so i can use it again.

 

[Fluff]

It doesn't mean anything, it's just a variable that stores the result of the filter function.

 

[Montanes]

It doesn't mean anything, it's just a variable that stores the result of the filter function.

Fair enough. Thanks very much for your help.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Montanes]

I am not sure if this thread is still live but it seems i have a colleague using 2016 and the above solution doesnt work for him!

Is it possible to have a solution that works for 2016 & 365?

 

[Fluff]

How about in A3 dragged down

=IFERROR(INDEX(Export!$F$2:$F$100,AGGREGATE(15,6,(ROW(Export!$L$2:$L$100)-ROW(Export!$L$2)+1)/(Export!$L$2:$L$100=$A$1),ROWS(A$3:A3))),"")

and change the F2:F100 range for the other 4 columns you need.