Creating a list from Table matches ?

[LlanfairPG]

Worksheet Help.png

If you look at the table above, i need a way of listing the people who have A/L against their name on a given day. Is there an easy formula / function to do this ? I have attached a sample file. The main file has a lot more lines to search from. But i'd like it so it produces a list as it finds them. Any help or guidance would be greatly appreciated.

[AliGW]

I’m sure there will be once you attach a sample workbook instead of an image.

Please go back and say ‘thanks’ in your previous thread and mark it as solved, if indeed it is: https://www.excelforum.com/excel-for...ct-syntax.html We expect some feedback when help is offered. Thank you.

[LlanfairPG]

I've just included a sample file.

[AliGW]

With the day you want in L3, try this:

=FILTER($C$4:$C$11,FILTER($D$4:$I$11,$D$3:$I$3=L3)="A/L")

AliGW on MS365 Insider (Windows) 32 bit

	L	M
3	MON	John Doe 1

Sheet: Sheet1

[LlanfairPG]

When i look at the formula it shows it as:

{=_xlfn._xlws.FILTER($C$4:$C$11,_xlfn._xlws.FILTER($D$4:$I$11,$D$3:$I$3=L3)="A/L")} ?

and gives me a #NAME? error ?

[AliGW]

Then you are not using MS365, as stated in your profile. We offer solutions based on that information. Which version do you have/are you using?

[LlanfairPG]

apologies, i go between versions on different computers. The one i'm currently using is 2016

[AliGW]

OK - I have run out of time now (going to watch some tennis). Hopefully someone else will pick this up.

[JohnTopley]

See attached

in K4

=IFERROR(INDEX($C$4:$C$11,AGGREGATE(15,6,(ROW($A$4:$A$11)-ROW($A$4)+1)/(D$4:D$11="A/L"),ROWS($1:1))),"")

copy across and down

[LlanfairPG]

Your formula works a treat. Thank you so much

[LlanfairPG]

How would i make it match more than 1 item, say instead of "A/L", if i had variants of "Day Off", "Off", "Off A", "Toil". But i wanted them all listed in the same list ?

[JohnTopley]

One way

=IFERROR(INDEX($C$4:$C$11,AGGREGATE(15,6,(ROW($A$4:$A$11)-ROW($A$4)+1)/((D$4:D$11="A/L")+(D$4:D$11="DAY OFF")),ROWS($1:1))),"")

Put criteria a range of cells (R1:R5)

=IFERROR(INDEX($C$4:$C$11,AGGREGATE(15,6,(ROW($A$4:$A$11)-ROW($A$4)+1)/((D$4:D$11=R$1)+(D$4:D$11=R$2)),ROWS($1:1))),"")

[LlanfairPG]

Thank you for your suggestion, but it seems to only work with the first term in the formula and ignores any others i try to add into it.

[JohnTopley]

See attached.

=IFERROR(INDEX($C$4:$C$11,AGGREGATE(15,6,(ROW($A$4:$A$11)-ROW($A$4)+1)/((D$4:D$11=$R$1)+(D$4:D$11=$R$2)),ROWS($1:1))),"")

My typo

[LlanfairPG]

Thank you so much it worked perfectly