# My array formula stops after it finds the first match....

#### 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 columns have values more than 0.
I am interested in the headers (row 1) of these columns.

The "Analysis" sheet should look as follows:
Book4
CDEFGHIJ
2Lot1234567
3Lot1Supplier1Supplier2Supplier5Supplier6
4Lot2Supplier5Supplier6
5Lot3
Analysis

Thus, what I need, is a formula that checks in sheet "1 on 1" in column B for "lot 1". If it finds that value, it has to check within that row to see whether any of the columns has values greater than 0.
The header (row 1) of the first column that has values within that row, should be transported back to the "analysis" sheet to cell E2.

Another formula should do the exact same thing, but should transport the header of the second column that has values within that row to cell F2. And so on...

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
Book4
BCDEFGHI
2Supplier1Supplier2Supplier3Supplier4Supplier5Supplier6Supplier7etc
3Lot100009505700
4Lot100007905700
5Lot141000045013500
6Lot100004801644
7Lot1000074059200
8Lot1012300007405642,5
9Lot100007402220
10Lot100007408598,8
11Lot200007008598,8
12Lot200007008598,8
13Lot200009707760
14Lot2000084540560
15Lot200008103796,47
16etc
1 on 1

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### pgc01

##### MrExcel MVP
Hi Remaniak

This is an example that you can adapt.

Remark:
...what I need, is a formula that ...

The formula with this new requirement of each Lot spanning several rows is geting bigger (and slower). Maybe instead of a formula solution you should start thinking of a vba solution.

Hope this helps
PGC

LUMN('1 on 1'!\$C\$3))),COLUMN('1 on 1'!\$C\$3:\$H\$20)-COLUMN('1 on 1'!\$C\$3))>0)),"",INDEX('1 on 1'!\$C\$2:\$H\$2,1+INDEX(SMALL(I
F(FREQUENCY(IF(('1 on 1'!\$B\$3:\$B\$20=\$C2)*('1 on 1'!\$C\$3:\$H\$20>0),COLUMN('1 on 1'!\$C\$3:\$H\$20)-COLUMN('1 on 1'!\$C\$3)),COLU
MN('1 on 1'!\$C\$3:\$H\$20)-COLUMN('1 on 1'!\$C\$3))>0,TRANSPOSE(COLUMN('1 on 1'!\$C\$3:\$H\$20)-COLUMN('1 on 1'!\$C\$3))),COLUMN('1

#### Remaniak

##### New Member
Thanks a lot!

This solution worked out perfectly!

Remaniak

Replies
21
Views
192
Replies
5
Views
84
Replies
0
Views
71
Replies
0
Views
30
Replies
6
Views
275

1,181,404
Messages
5,929,750
Members
436,687
Latest member
Glass of Gin

### 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