One Array function using INDEX, SMALL, IF works but the other doesn't

Question

I'm hoping someone can figure this out for me, because I'm stumped!

I have an excel tab called Data, that is holding all the data I want to look at.

Check (A)	Sector (B)	Sub/Sector(C)	Overarching Question (D)	Q# (E)	Questions (F)	BHA_Tool (G)
True	S1	Sector:A	Is this a new methodology, tool, or technique related to agricultural production?	S1q1	Is this a new methodology, tool, or technique related to agricultural production?	6 Other Source
True	S1	Sector:A	Do the targeted participants have experience in the proposed activity?	S1q2	What are the types of post-production processes your household currently utilizes	3 FGD - Pop
True	S1	Sector:A	Do the targeted participants have experience in the proposed activity?	S1q3	What are the types of grain storage your household currently uses	3 FGD - Pop

Table of data in DATA tab

In tab Full_Questions I query data using the following array:

{=IFERROR(INDEX(Data!F$2:F$300,SMALL(IF(Data!A$2:A$300=TRUE,ROW(Data!F$2:F$300)),ROW(1:1))-1,1),"")}

And it works fine.

Full List of Q (A)	To Be asked of... (B)	Available in tool.. (C)
Is this a new methodology, tool, or technique related to agricultural production?	Project Documents	6 Other Source
What are the types of post-production processes your household currently utilizes	Farming Households; disag by gender, age range, etc	3 FGD - Pop
What are the types of grain storage your household currently uses	Farming Households; disag by gender, age range, etc	3 FGD - Pop

Table of data in Full_Questions, which is queried from Data tab by either an array (column A) or INDEX MATCH (columns B and C)

On the next tab: FGD - Pop, I try to narrow down the data using the following array formula:

{=IFERROR(INDEX(Full_Questions!A$2:A$300,SMALL(IF(Full_Questions!C$2:C$300="3 FGD - Pop",ROW(Full_Questions!A$2:A$300)),ROW(1:1))-1,1),"")}

But I don't get anything back.

I thought it could be that I can't query data with an array from another array so I tried:

{=IFERROR(INDEX(Data!F$2:F$300,SMALL(IF(AND(Data!A$2:A$300=TRUE,Data!G$2:G$300="3 FGD - Pop"),ROW(Data!F$2:F$300)),ROW(1:1))-1,1),"")}

But that also didn't work.

What am I doing wrong? Any suggestions would be greatly appreciated!

Answer 1

Trim the "FGD - Pop" field / data (at source and in the respective function"

Context

I'm not allowed to say "I like the function construct you've used - i.e. the combination of index/small etc. to bring back unique index/match results - great initiative" because it'll be edited out on account of not being 'robotic' enough in our replies (so will repeat this self-same sentiment in my comment below - ha!).

Actions

I've replicated all of your inputs / functions in this google workbook (filled in the lookup on Pop-3g in Full_Questions too, which I believe would've been sourced from the Data tab in the first instance. Anyways.

Upon doing this (and fixing up the small issues re: extra spaces - e.g. present in the FGD-Pop data), I get your function working fine in the FGD-Pop tab.

I've also included a slightly quicker/easier way to achieve what you've done using the filter function red font).

Results

Screenshots refer to the relevant functions if you've got Office 365 and can use Filter functionality (if you don't have it, I advise you see if you are able to, its' got some wicked new functions like sort, sortby, unique etc.).

Screenshots

Data tab

Full_Questions

[

You didn't ask for this, but in case you make use of the filter function here it is for your convenience sir!

=FILTER(Data!F1:F500,Data!A1:A500=TRUE)

And the one you've been waiting for....☺

FGD - Pop tab: with your function

(after correcting the extra space issue in "3 FGD - Pop" filer (at source and in the function itself for consistency)

Function in question:

=IFERROR(INDEX(Full_Questions!$A$1:$A$10,SMALL(IF(Full_Questions!$C$1:$C$10="3 FGD - Pop",ROW(Full_Questions!$A$1:$A$10)),ROW(1:1))-1,1),"")

FGD - Pop tab: with Office 365 "Filter" method

Function:

=FILTER(Full_Questions!B1:B3,1*(Full_Questions!C1:C3="3 FGD - Pop"))