INDEX Functions with multiple criteria

[gibson8]

I am trying to use the INDEX functions to extract multiple data with multiple criteria from different work sheet.

But it didn't work, it show empty cells, no error was given about the formula.

Please kindly advise and see the formula and table below.

A column	B Column	C Column	D Column	E Column
6w46wr1	2023	John S	December	555
64631321	2023	Smith N	December	666
27537	2022	Nana San	October	777
737837	2023	John S	December	6565
437878	2023	Nana San	December	888
dsfsg1	2022	Nana San	December	999
kku123	2023	Smith N	July	111
rtete1233	2024	Smith N	December	222

Smith N , 2023, December
=IFERROR(INDEX($A$2:$A$17,SMALL(IF(($C$2:$C$17=Smith N)*($B$2:$B$17=2023)*($D$2:$D$17=December), ROW($A$2:$A$17)),ROW(1:1))-1,1),"")

Since the table is in another sheet with same workbook.
but I tried the formula above, the cells still give me empty data.

 

[Special-K99]

=IFERROR(INDEX($A$2:A$100,AGGREGATE(15,6,ROW($A$2:$A$100)/(($C$2:$C$100="SMITH N")*($B$2:$B$100=2023)*($D$2:$D$100="December")),ROWS($A$2:A2))-(2-1),COLUMNS($A2:A2)),"")

 

[Alex Blakenburg]

If you don't need it to be backward compatible and can just use 365 functions this should work:

=FILTER($A$2:$E$17,($C$2:$C$17="Smith N")*($B$2:$B$17=2023)*($D$2:$D$17="December"),"")

PS: change the E to A if you only want the value in column A returned.

 

[gibson8]

=IFERROR(INDEX($A$2:A$100,AGGREGATE(15,6,ROW($A$2:$A$100)/(($C$2:$C$100="SMITH N")*($B$2:$B$100=2023)*($D$2:$D$100="December")),ROWS($A$2:A2))-(2-1),COLUMNS($A2:A2)),"")

thank you @Special-K99, at last i figured out that the issue is different format of the cells from the original data.
and it also happens on your formula, if the data is in different format (some are in General format, some Text format, some Number format), show data won't show up in the index formula.

 

[gibson8]

If you don't need it to be backward compatible and can just use 365 functions this should work:

=FILTER($A$2:$E$17,($C$2:$C$17="Smith N")*($B$2:$B$17=2023)*($B$2:$B$17=2023)*($D$2:$D$17="December"),"")

PS: change the E to A if you only want the value in column A returned.

thank you @Alex Blakenburg, what if i want to sort out similar data but not only December, and also October. it didn't work even i added *($B$2:$B$17=2023)*($D$2:$D$17="October") after December one.

 

[Phuoc]

=FILTER($A$2:$E$17,($C$2:$C$17="Smith N")*($B$2:$B$17=2023)*(($D$2:$D$17="December")+($D$2:$D$17="October")),"")