Array Formula Help

[dmurphy1991]

Hi, so i'm trying to include an array formula in my workbook (will eventually be automated), all the example i can find have data starting in a1, my data doesn't and i can't get the formula to work as it should. It's fine if i put my data into A1 and then eneter the formula, but i can't work out why the array range's row starting position affects the formula.

My array range is p7:Q24

yet if i move the array's range to P1:Q18 it works!?

My formula is:
{=IFERROR(INDEX($P$1:$Q$18,SMALL(IF($P$1:$P$18=W$3,ROW($P$1:$P$18)),ROW(1:1)),2),"")} - for the one that works.

But changing the array range just means it returns the wrong info

Any help would be greatly appreciated!

 

[MarcelBeug]

Try a simple VLOOKUP:

=IFERROR(VLOOKUP(W$3;$P$7:$Q$24,2,0),””)

 

[dmurphy1991]

I have multiple values matching the serach criteria in the same array range though, hence my use of an array. i actually just solved the issue myself by playing around, i thought it would be a ROW offset somewhere but i couldn't figure out where until now...

{=IFERROR(INDEX($P$7:$Q$24,SMALL(IF($P$7:$P$24=W$3,ROW($P$7:$P$24)-6),ROW(1:1)),2),"")}

the "-6" solved my issue, not 100% what the issue was but knew i had to offset a reference somewhere!

 

[FormR]

the "-6" solved my issue, not 100% what the issue was but knew i had to offset a reference somewhere!

This version would be more robust, change the X2 references to the cell in which you place the formula:

=IFERROR(INDEX($P$7:$Q$24,SMALL(IF($P$7:$P$24=W$3,ROW($P$7:$P$24)-ROW($P$7)+1),ROWS($X$2:X2)),2),"")

 

[MarcelBeug]

Even better is to replace -6 with -ROW($P$7)+1. This will make the fomula dynamic.
Otherwise I have my doubts about the ROW(1:1) part, because it looks to me that it will return always the same result.
That's why I suggested the VLOOKUP.
I would expect something like ROW(P7)-ROW($P7)+1

 

[dmurphy1991]

This version would be more robust, change the X2 references to the cell in which you place the formula:

=IFERROR(INDEX($P$7:$Q$24,SMALL(IF($P$7:$P$24=W$3,ROW($P$7:$P$24)-ROW($P$7)+1),ROWS($X$2:X2)),2),"")

Would that not result in a circular reference as i'm referencing to the cell i'm entering the formula into?

 

[FormR]

Would that not result in a circular reference as i'm referencing to the cell i'm entering the formula into?

You could try it - but no, the ROWS() function won't result in a circular reference.

 

[dmurphy1991]

Even better is to replace -6 with -ROW($P$7)+1. This will make the fomula dynamic.
Otherwise I have my doubts about the ROW(1:1) part, because it looks to me that it will return always the same result.
That's why I suggested the VLOOKUP.
I would expect something like ROW(P7)-ROW($P7)+1

The formula that i have works and can be dragged down and across - so it looks up varying strings in the array according to the row 3 reference. Just having trouble entering it into vba using stored ranges now:
Dim laddercount As Double
Dim x, y As Double
laddercount = Evaluate("COUNTA(R:R)-1")

For y = 19 To laddercount + 19
For x = 7 To x + laddercount
Cells(x, y).Select

Selection.FormulaArray = "=IFERROR(INDEX(arrayrange,SMALL(IF(ladderrange = Range(y&""$6""),ROW(ladderrange)-6),ROW(1:1)),2),"")"

Above is what i have but keep getting the run-time error 1004