Using an Array to return multiple values

[Kristinmc]

Hello!

I am trying to get an array formula to work so I can have one value return multiple answers.

My formula currently is:

=INDEX('GP Serial Data'!$F:$G,SMALL(IF((Summary!$B$2='GP Serial Data'!$F:$G),ROW('GP Serial Data'!$F:$G)-MIN(ROW('GP Serial Data'!$F:$G))+1,""),ROWS($A$1:A1)))

I'm not sure I have the rows correct. My lookup value is in B2. I want the return values to start in B5.

The data I have is looking to reference an Item number and all the serial numbers for that one item number.

The range of the data could change, which is why I was using just the GP SERIAL DATA $F:$G (columns) instead of $F$1:$G$30000 (set range).

I can't get this to work--any help would be great!

 

[sgroath]

Assuming you have item numbers in col F and serial numbers in col G, this should be:

=IFERROR(INDEX(G:G,SMALL(IF(($B$2=F:F),ROW(F:F)-MIN(ROW(F:F))+1,""),ROWS($B$5:B5))),"")

and then instead of pressing enter, press Ctrl+Sift+Enter to make it an array function which will add curly braces around the formula.

I also assumed this is all on the same worksheet. If your data is on a different tab, you will need to add the 'GP Serial Data'! before your F and G column references.

 

[sgroath]

Also, doing this against the full columns is not a good practice. You may want to make your data into a table instead which can flex with data size.