Array Index Small

[SimonVOwen]

Good Morning All,

I am being very silly can anyone assist.

{=IFERROR(INDEX(sheet1!$D$2:$D$2872,SMALL(IF(sheet1!$C$2:$C$2872='Central Data'!$A2,ROW(sheet1!$D$2:$D$2872)),B$1)),"")}

I am using this array formula to pull data for another sheet, as there are multiple values present so i am dragging this formula across to pull out the 2nd/3rd results etc

{=IFERROR(INDEX(sheet1!$D$2:$D$2872,SMALL(IF(sheet1!$C$2:$C$2872='Central Data'!$A2,ROW(sheet1!$D$2:$D$2872)),C$1)),"")}

In this code the B1 / C1 are just referencing 1/2/3 etc as i believed this was pulling the 1st/2nd/3rd results.
This code is pulling out data however it is pulling the data from 1 row below the value i want..
What stupid thing have i done!

Thanks for any assistance.

Regards,
Disgruntled Muppet

 

[AlanY]

deleted

 

[AlanY]

this should work

{=IFERROR(INDEX(sheet1!$D$1:$D$2872,SMALL(IF(sheet1!$C$1:$C$2872='Central Data'!$A2,ROW(sheet1!$D$1:$D$2872)),B$1)),"")}

 

[SimonVOwen]

Cheers! Knew i was being stupid :/
Have a good day!

 

[FormR]

{=IFERROR(INDEX(sheet1!$D$2:$D$2872,SMALL(IF(sheet1!$C$2:$C$2872='Central Data'!$A2,ROW(sheet1!$D$2:$D$2872)),B$1)),"")}

Hi, to be robust against row insertions breaking your formula, you should use.

{=IFERROR(INDEX(Sheet1!$D$2:$D$2872,SMALL(IF(Sheet1!$C$2:$C$2872='Central Data'!$A2,ROW(Sheet1!$D$2:$D$2872)-MIN(ROW(Sheet1!$D$2:$D$2872))+1),B$1)),"")}