# My array formula stops once the first row matches...

#### Remaniak

##### New Member
Hi All,

I am in sheet "Analysis" and my data is stored in sheet "1 on 1" (example of the data is shown at the bottom).

What I need to know is for "lot 1", which column headers in row 1 have values more than 0.

I am using the following formula:

=IF(COLUMNS(\$E141:E141)>SUMPRODUCT(--(INDEX('1 on 1'!\$B\$2:\$EQ\$50000;MATCH("lot 1";'1 on 1'!\$B\$2:\$B\$50000;0);0)>0));"";INDEX('1 on 1'!\$B\$1:\$EQ\$1;SMALL(IF(INDEX('1 on 1'!\$B\$2:\$EQ\$50000;MATCH("lot 1";'1 on 1'!\$B\$2:\$B\$50000;0);0)>0;COLUMN(\$B:\$EQ)-COLUMN(\$B:\$B)+1);COLUMNS(\$E141:E141)))))

However, it gives me supplier 5, and supplier 6. While supplier 1 and 2 also have values more than 0, but are several rows lower than supplier 5 and 6, and thus are not included anymore.

It there a workaround for this?

Thanks.

Remaniak
Book1
BCDEFGH
1Supplier1Supplier2Supplier3Supplier4Supplier5Supplier6
2Lot100009505700
3Lot100007905700
4Lot141000045013500
5Lot100004801644
6Lot1000074059200
7Lot1012300007405642,5
8Lot100007402220
9Lot100007408598,8
10Lot200007008598,8
11Lot200007008598,8
12Lot200009707760
13Lot2000084540560
14Lot200008103796,47
Sheet1

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Krishnakumar

##### Well-known Member
Hi,
Book1
BCDEFGHIJK
1012  344
2Supplier 1Supplier 2Supplier 3Supplier 4Supplier 5Supplier 6Lot 1Supplier 1
3Lot 100009505700Supplier 2
4Lot 100007905700Supplier 5
5Lot 141000045013500Supplier 6
6Lot 100004801644
7Lot 1000074059200
8Lot 1012300007405642,5
9Lot 100007402220
10Lot 100007408598,8
11Lot 200007008598,8
12Lot 200007008598,8
13Lot 200009707760
14Lot 2000084540560
15Lot 200008103796,47
Sheet2

Formulas

In C1 and copied across,

=IF(SUMPRODUCT(--(\$B\$3:\$B\$15=\$J\$2),C\$3:C\$15)>0,LOOKUP(9.9999E+307,\$B\$1:B1)+1,"")

J1:

=LOOKUP(9.99999E+307,B1:H1)

K2 and copied down,

=IF(ROWS(\$A\$2:\$A2)<=\$J\$1,LOOKUP(ROWS(\$A\$2:\$A2),\$C\$1:\$H\$1,\$C\$2:\$H\$2),"")

HTH

#### Remaniak

##### New Member
Thanks a lot!

However, the overview I need to make in the Analysis sheet is the following:
Book2
CDEFGHIJ
2Lot1234567
3Lot1Supplier1Supplier2Supplier5Supplier6
4Lot2Supplier5Supplier6
5Lot3
Sheet1

Also, I do not know what rows contains which lots. Is there no way to modify the array formula to not just check the first row it finds for "lot 1" but also the next rows with "lot 1"?

The formula was:

=IF(COLUMNS(\$E141:E141)>SUMPRODUCT(--(INDEX('1 on 1'!\$B\$2:\$EQ\$50000;MATCH("lot 1";'1 on 1'!\$B\$2:\$B\$50000;0);0)>0));"";INDEX('1 on 1'!\$B\$1:\$EQ\$1;SMALL(IF(INDEX('1 on 1'!\$B\$2:\$EQ\$50000;MATCH("lot 1";'1 on 1'!\$B\$2:\$B\$50000;0);0)>0;COLUMN(\$B:\$EQ)-COLUMN(\$B:\$B)+1);COLUMNS(\$E141:E141)))))

Thanks!

#### Krishnakumar

##### Well-known Member
Hi,

In K2 and copied across,

=INDEX(\$A\$1:\$H\$1,SMALL(UNIQUEVALUES(IF(\$B\$2:\$B\$14=\$J2,IF(\$C\$2:\$H\$14>0,COLUMN(\$C\$1:\$H\$1)))),COLUMNS(\$K\$2:K\$2)))

where j2 houses Lot 1

Must be confirmed with CTRL+SHIFT+ENTER

HTH

Replies
7
Views
237
Replies
0
Views
299
Replies
2
Views
285
Replies
3
Views
231
Replies
9
Views
496

1,181,100
Messages
5,928,058
Members
436,586
Latest member
latintxn

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back