Array Formula Extract Records tutorial not working for me

[Malhalar]

Having watched a MrExcel YouTube video about pulling records based on a condition, I attempted to reorganise a large question bank we have for learners. Unfortunately, the formula doesn't seem to work as it's getting caught pulling correct row numbers and always returns 0.

=IFERROR(SMALL(IF($D$1:$D$100=$J$1,ROW($A$1:$A$100)-1),ROW(A1)),"").

Column A - ID's
Column D - Categories
J1 - Category filter.

Any help is massively appreciated! Original tutorial link below:

 

[Fluff]

Can you post some sample data, along with what you have tried.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Malhalar]

So annoyingly, it looks like it works on my laptop but not on the work computer. I wonder if there may be some workplace restrictions in place for formula. This video is 7 years old so pre 2016, my work computer is using excel 2016 and my laptop (where the below was attempted) is 365. Surely there shouldn't be any issues caused by the product?

Book1
	A	B	C	D	E	F	G	H	I
1	Question Number	Category	Question	Stage_Category	Answer		Stage 1	Question	Answer
2	1	Geography	What is the capital of England?	Stage 1	London		1	What is the capital of England?	London
3	2	Personal	What is my favourite colour?	Stage 1	Blue		2	What is my favourite colour?	Blue
4	3	Politics	Who is the president of US?	Stage 2	Joe Biden
5	4	Famous People	Who is the father of history?	Stage 2	Herodotus
6	5	Animals	What animal has orange fur and black stripes?	Stage 3	Tiger
7	6	Animals	What animal is known as a leviathan?	Stage 3	Whale
8	7	Geography	What is the capital of Tasmania?	Stage 3	Hobart
9	8	Maths	What is 2+2?	Stage 4	4
10	9	Maths	What is the square root of 25?	Stage 4	5
11	10	Personal	What is the colour of my hair?	Stage 4	Brown
Sheet1

Cell Formulas
Range	Formula
G2:G11	G2	=IFERROR(SMALL(IF($D$2:$D$11=$G$1,ROW($A$2:$A$11)-1),ROW(A1)),"")
H2:H11	H2	=IFERROR(VLOOKUP($G2,$A:$E,3,FALSE),"")
I2:I11	I2	=IFERROR(VLOOKUP($G2,$A:$E,5,FALSE),"")

 

[Fluff]

If you are using that formula in 2016 you need to confirm it with Ctrl Shift Enter, rather than just Enter.
If done correctly you will see the formula wrapped in the curly braces { } as can bee seen in the video

 

[Malhalar]

If you are using that formula in 2016 you need to confirm it with Ctrl Shift Enter, rather than just Enter.
If done correctly you will see the formula wrapped in the curly braces { } as can bee seen in the video

You are a hero! Thank you so much. I thought the curly braces were to show the formula rather than the result. Thanks again!

 

[Fluff]

You're welcome & thanks for the feedback.