Array Formula Change

[ExcelRoy]

Hi all,

I have an array formula which looks up data from within the range AA219:AA2217

What I would like is that this formula works in the same way but every other row and not consecutive rows

If I use this formula and drag down it works fine, but if I use this formula every other row it misses out half the data I actually need it to show


=IF($R$2<>"",IFERROR(INDEX($AF$219:$AF$2217,SMALL(IF($AA$219:$AA$2217=$R$2,ROW($AA$219:$AA$2217)-ROW($AA$219)+1),ROWS(B$10:B10))),""),"")


Many Thanks for any help

 

[Aladin Akyurek]

In B10 control+shift+enter, not just enter, and copy down:

=IF($R$2<>"",IFERROR(INDEX($AF$219:$AF$2217,SMALL(IF(MOD(ROW($AA$219:$AA$2217)-ROW($AA$219),2)=0,IF($AA$219:$AA$2217=$R$2,ROW($AA$219:$AA$2217)-ROW($AA$219)+1)),ROWS(B$10:B10))),""),"")

 

[ExcelRoy]

Hi Aladin,

Many thanks for the reply

Unfortunatley this halves the available data again?

Very Odd, but thanks for trying

 

[Aladin Akyurek]

Hi Aladin,

Many thanks for the reply

Unfortunatley this halves the available data again?

Very Odd, but thanks for trying

I don't get what you are trying to say... Maybe you can create a scaled-down sample along with the desired results?

 

[circledchicken]

Hi,

Perhaps you mean something like this (with CTRL-SHIFT-ENTER):

=IF(
    OR(
      $R$2 = "",
      MOD(ROWS(B$10:B10), 2) = 0),
    "",
    IFERROR(
      INDEX(
        $AF$219:$AF$2217,
        SMALL(
          IF(
            $AA$219:$AA$2217 = $R$2,
            ROW($AA$219:$AA$2217) - ROW($AA$219) + 1),
          ROUNDUP(ROWS(B$10:B10) / 2, 0))),
      ""))

 

[Aladin Akyurek]

@circledchicken

Indeed: 'Every other' apparently holds for the output, not for the input.

 

[ExcelRoy]

Many thanks for your help, much appreciated

 

[circledchicken]

You're welcome.