Array formula returns wrong results when source data is moved from the top row

[kolokoy]

Hi,

I have been looking online on how to fix my formula. It is an array formula that will only give correct results when the data is at the TOP row, that is row 1, regardless of the column. When I cut and paste the formula below row 1, the results are wrong.

Here is my formula:

Please Login or Register  to view this content.

The source data is in a sheet named DATA. I attached the Excel file with the link below:
http://www.excelforum.com/attachment...1&d=1468392371

Can you please help me fix my array formula?
The goal of the array formula is to list the serial code (first column) if the value in the third column is NOT zero.

Thank you very much.

[Tony Valko]

Not real sure what you're trying to do.

Since you're using Excel 2010 you can use the IFERROR function and reduce the formula length by half...

Array entered in B13 and copied down:

=IFERROR(INDEX(Data!$G:$G,SMALL(IF(Data!$J$1:$J$152<>0,ROW(Data!$J$1:$J$152)),ROWS(B$13:B13))),"")

[jewelsharma]

try the following Array Formula:

Please Login or Register  to view this content.

[kolokoy]

Not real sure what you're trying to do.

Since you're using Excel 2010 you can use the IFERROR function and reduce the formula length by half...

Array entered in B13 and copied down:

=IFERROR(INDEX(Data!$G:$G,SMALL(IF(Data!$J$1:$J$152<>0,ROW(Data!$J$1:$J$152)),ROWS(B$13:B13))),"")

Hi Tony,

Thank you for your suggested formula. Indeed it is shorter and it worked the same as my original formula, so the problem is the same.
The formula (mine and your shortened version) actually works, but the problem is, when I MOVE the source data through CUT and PASTE to another location in the sheet. The formula gives wrong results.

For instance,in the sample file I attached, the SOURCE data is in DATA sheet located in range G1:J152. When I cut and paste that range (G1:J152) to say G10:J161, your formula still WORKED, but mine DIDN'T!

However, there is one more problem, when I cut the source data and paste NOT in the G column, say in A10:D161, your formula and mine both give wrong results!

I believe the problem is in the last part of the formula that says ROWS(B$13:B13)? But I am not really sure how to make the formula give the same correct results even if you CUT and PASTE the source range to another location.

Thank you very much.

[kolokoy]

try the following Array Formula:

Please Login or Register  to view this content.

Thank you Jewel for your reply. I tried the formula you gave me, with the source data in range D1:J152, the formula showed results of 0.

[jewelsharma]

oops, wrong range. try this:

Please Login or Register  to view this content.

[Tony Valko]

I believe the problem is in the last part of the formula that says ROWS(B$13:B13)?

No, ROWS(B$13:B13) is just an incrementer. The reference should be to the first cell the formula is entered into. If the first cell you enter the formula into is K27 then it should be ROWS(K$27:K27).

Try this...

=IFERROR(INDEX(Data!$G$1:$G$152,SMALL(IF(Data!$J$1:$J$152<>0,ROW(Data!$J$1:$J$152)-MIN(ROW($J$1:$J$152))+1),ROWS(B$13:B13))),"")

Change the highlighted portion to correspond to the first cell the formula is entered into.

Still array entered.

[kolokoy]

oops, wrong range. try this:

Please Login or Register  to view this content.

Thank you very much Jewel! This formula worked the way I really wanted!

[kolokoy]

No, ROWS(B$13:B13) is just an incrementer. The reference should be to the first cell the formula is entered into. If the first cell you enter the formula into is K27 then it should be ROWS(K$27:K27).

Try this...

=IFERROR(INDEX(Data!$G$1:$G$152,SMALL(IF(Data!$J$1:$J$152<>0,ROW(Data!$J$1:$J$152)-MIN(ROW($J$1:$J$152))+1),ROWS(B$13:B13))),"")

Change the highlighted portion to correspond to the first cell the formula is entered into.

Still array entered.

Thank you Tony for the formula. But still it did not work. I used Jewel's formula above and it worked exactly the way I need it to work. Thank you very much for your input!

[Tony Valko]

Good deal. Thanks for the feedback!