Index lookup change data

[John]

I am trying to take this formula and move it from on column to another and
only change one value. When I go to edit the formula it removes the "{}"
around the formula. The formula works in the original cells but If I add the
"{}" it will not caluculate.

{=IF(ISERROR(INDEX($T$1:$W$2500,SMALL(IF($T$1:$T$2500=$A3,ROW($T$1:$T$2500)),ROW($8:$8)),4)),"",(INDEX($T$1:$W$2500,SMALL(IF($T$1:$T$2500=$A3,ROW($T$1:$T$2500)),ROW($8:$8)),4)))}


I just want to change row 8:8 to row 9:9 and so on. If I edit the original
cell it changes the formula to remove the "{}" and the formula will no longer
pull the data. Please help with how to correct this issue. Thanks

 

[T. Valko]

The squiggly brackets { } denote an array formula.

**Array formulas are entered differently than a regular formula. After you
type in a regular formula you hit the ENTER key. With an array formula you
*must* use a combination of keys. Those keys are the CTRL key, the SHIFT key
and the ENTER key. That is, hold down both the CTRL key and the SHIFT key
then hit the ENTER key.

When done properly Excel will enclose the formula in squiggly brackets { }.
You can't just type these brackets in, you *must* use the key combo to
produce them. Also, anytime you edit an array formula it *must* be
re-entered as an array using the key combo.

As far as the formula itself, it can be made much more efficient.

I'd use an intermediate cell to count how many records meet the criteria...

Enter this formula in cell A1:

=COUNTIF(T1:T2500,A3)

Then, you refer to that cell and you can replace all this:

{=IF(ISERROR(INDEX($T$1:$W$2500,SMALL(IF($T$1:$T$2500=$A3,ROW($T$1:$T$2500)),ROW($8:$8)),4))

With something like this:

=IF(ROWS(A$1:A8)>$A$1,"",........

Here's the entire array entered** formula:

=IF(ROWS(A$1:A8)>$A$1,"",INDEX($T:$T,SMALL(IF($T$1:$T$2500=$A3,ROW(T$1:T$2500)),ROWS(A$1:A8))))

 

[T. Valko]

Ooops!

Correction. I indexed the wrong column. Should be (still array entered):

=IF(ROWS(A$1:A8)>$A$1,"",INDEX($W:$W,SMALL(IF($T$1:$T$2500=$A3,ROW(T$1:T$2500)),ROWS(A$1:A8))))